: Exactly my point... No matter how much you think you thought about

: something- you still missed something. Your SS# example is an excellent

one

: to show how something that is so fixed.. so exacting.. so controlled...

: STILL can make a bad PK.





Daniel Walker (one of the moderators) posted this, in response to someone's

query about the same thing. (This was from about 18 months ago, but it's a

good explanation):



<quote>

> Let me explain:

> A semantic key is a value from the real world which appears to identfy a

> record in a unique way - a National Insurance number in an employee

> database, for instance. Everyone has an NI value, and no one can share it,

> right? Right?

>

> Reality always throws up exceptions. Say you employ a foreign worker on a

> temporary visa, for example - no NI No., suddenly no primary key. Even in

> the best of worlds, you now find yourself _inventing_ imanginary-real

> values for these records - which is only cool until someone forgets that

> it's an imaginary value and fills out an NI return for the guy. In the

> worst of worlds, your DB screws up and starts applying the

> same NI value to all the overseas workers, or you end up hiring someone

> who _actually_has_ the NI no. you just applied to Mr Temp.

>

> Semantic keys are a fundamental bad idea in database design and should

> never be used. Always use an autonumber field as the primary key.

</quote>



Primary keys should be used to identify records uniquely. Best to stick to

autonumbers/identity. People like to use things like SSN, Tax File Numbers,

ISBNs etc as primary keys because it helps them visually associate records

with their PK values. That, IMHO, is not what a PK is for. A PK identifies a

record uniquely - it doesn't help you remember which data is associated with

which PK value...



Cheers

Ken

When I first started working with database design I tried to avoid autokey 

fields for the same reasons but experience has taught me better. I now use 

look up lists and queries to help me make associations between my data.

-pete



> > Semantic keys are a fundamental bad idea in database design and should

> > never be used. Always use an autonumber field as the primary key.



> People like to use things like SSN, Tax File Numbers,

> ISBNs etc as primary keys because it helps them visually associate 

records

> with their PK values. That, IMHO, is not what a PK is for. A PK 

identifies a

> record uniquely - it doesn't help you remember which data is associated 

with which PK value...

> 

> Cheers

> Ken

Wow... "never" is quite the strong word!  (Thanks for posting Daniel 

Walker's archived thread, Ken -- really opened my eyes!)



I have enjoyed this thread immensely - it certainly has taught me a lot.  

I already feel more valuable as a database developer just having gotten 

this quandary settled, once and for all!



My gratitude goes to everyone who weighed in --  I'll go into my next 

project with a much better design plan, thanks to all of you :).



Susan