Re: Surrogate Key vs Production Key

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 21 Oct 2004 15:31:39 -0500
Message-ID: <uy8hz3kmb.fsf@standardandpoors.com>

On 21 Oct 2004, ed.prochak_at_magicinterface.com wrote:
> wizofoz2k_at_yahoo.com.au (Noons) wrote in message

>> There is no such criticism.  No one can expect a prescient
>> knowledge about a complex system of interactions.  The maths
>> alone to allow for that, would make most systems not viable
>> economically.  What is expected however is a flexible design
>> that can be adapted with minimal impact to new discoveries
>> about that system.  And surrogate keys lend themselves
>> perfectly to that.

>
> Can you give an example of how you handled this? How does
> Surrogate keys fit in a flexible design in a greater way than
> Natural keys? How about this for raw, very high level entities:
> Customer, PhoneNumber, Address. If you were working on a
> billing system DB for the phone company how might you make a
> model that allowed change while using surrogate keys? How would
> an equivalent natural key model not be as flexible?

Here's my stab,

Basically, surrogate keys allow you to model the business and start implementation without fully knowing the actual natural keys of the source data. For example, we have a system that has to model a loan (among other things). For one grouping of customers (defined by the market they are supporting), the loan has a natural key, deal/collateral_id while for another grouping of customers, the natural key is their internal id for the loan. Our database stores the data for both customers unifying it for presentation back to them. Both groups of customers want our analytic engine but they have different ways to key their data. Plus, we have a third product that is on the horizon which will have an unknown set of data files, but they will also interact with loans, plus, we will have loan rows created directly by our application.

Its easy to model the business and say that each loan has an id and that a loan_id is related to a property through our property_loan table. We immediately where able to design that, and then we were able to immediately start building our application.

What I then did was had a schema for translating the "natural key" to our internal key. Then, as new "natural keys" show up, we code that specific layer. The end result is that we have one surrogate key for the application but we can always get at its natural key.

-- 
Galen Boyer

Received on Thu Oct 21 2004 - 15:31:39 CDT