Re: Surrogate Key vs Production Key

jcelko212_at_earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0410130643.1a5f78f7_at_posting.google.com>...

>
> There are a lot of changes coming in many of the standard codes. The
> world is bigger and more interconnected than before. The beautiful
> part about industry standard codes is that:

Yeah, it's gonna be interesting to watch how many massive updates those changes are gonna mean for people using the codes as natural keys. Think of all the FKs that will need tweaking and updating. What a flexible design...

>
> 1) They can be verified with a trusted source or on the physical
> entity

They can be verified as well if surrogate keys are used instead. A VIN doesn't stop being itself because it is not used as part of a PK.

> 2) They usually have a validation rule (check digit, regualr
> expression, etc.)

In other words: an implicit "structure" as opposed to an explicit specification. I can hear the alarm sirens already! A single attribute (the base of any column used as a PK or FK) should not have an inherent "structure". If it does, then there is some normalization missing in there somewhere. Using it as a key (PK or FK is immaterial) should sound alarm bells everywhere.

> 3) Some other poor bastard is doing all the work of maintaining them
> for me.

NOW ya tawkin! :)
Hope that poor sod knows how to write fast SQL and how to do a deferred constraint quickly. Or else there is gonna be heck to pay in processing overhead... Received on Thu Oct 14 2004 - 06:07:12 CDT