Re: Surrogate Key vs Production Key

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:73e20c6c.0410120137.1af7003d_at_posting.google.com... | Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u655hmml9.fsf_at_standardandpoors.com>...

|
| > Maybe someone needs to define a "natural key".  Seems to me, it
| > is a key that some other system has already defined.
|
| The actual choice of a natural key can be in fact a very
| nasty problem.  And by no means easy.  Some times what
| appears to be a sensible choice turns up to be a disaster
| a few months down the line.
|

i'm jumping into this one a little late, so maybe i've missed a few comments...

'natural key' seems to be interpreted by some here as a user-assigned (or externally-assigned) key and surrogate key seems to be interpreted as a system-assigned

the real issue is between natural key structures and surrogate key columns

implementation options will always be debatable, and compromises (not design choices, always compromises) will need to be made when implementing the conceptual data model

but when establishing the data model, introducing a surrogate key column to replace a natural key column structure seriously weakens the data model and often hides (or impedes discovery of) very important facts about the business

an example way back from Oracle's ERA class is the vehicle/driver example, that i remember went something like this:

vehicle
- veh_id -- pk

• ...

department
- dept_id -- pk

• ...

department_vehicle
- dept_id -- pk, fk to department

• veh_id -- pk, fk to vehicle
• ...

person
- pers_id -- pk

• dept_id -- fk to department, fk to department_vehicle (with veh_id)
• veh_id -- fk to department_vehicle (with dept_id)
• ...

by modeling this without a surrogate key, the business rule 'a person can only be assigned to use a vehicle that is assigned to her department' is easily enforced via the relationship 'each person may currently be assigned one and only one department vehicle'

modeling with a surrogate key, you get the structure:

department_vehicle
- dept_veh_id pk

• dept_id -- fk to department
• veh_id -- fk to vehicle
• ...

person
- pers_id -- pk

• dept_id -- fk to department
• dept_veh_id -- fk to department_vehicle
• ...

which does not in itself document, enforce, describe, or inform anything specific about the person/department vehicle relationship and the person's assigned department

if the business rule is suspected to have high volatility, then an alternate structure (without surrogate keys) could be used, with a constraint to enforce reporting_dept_id = vehicle_dept_id

person
- pers_id -- pk

• reporting_dept_id -- fk to department
• vehicle_dept_id -- fk to department_vehicle (with veh_id)
• veh_id -- fk to department_vehicle (with vehicle_dept_id)
• ...

time and time again i have seen very poor data models developed because an organization standardized on surrogate keys and replaced every multi-column primary key with a single-column surrogate key as soon as the entity was discovered

surrogate keys are, and always will be, compromises for the benefit of a specific implementation -- they should not be introduced into the conceptual data model, and when introduced into the logical (or physical, depending on your semantics) data model, exactly what they are replacing should be thoroughly documented

++ mcs Received on Tue Oct 12 2004 - 07:48:37 CDT