I've been both creating and using databases heavily for 17 years in business,
personal and organizational environments. And I've seen both ways done.
I'd like to weigh in on the side of primary keys not containing real world
data. If it's real world data, there will inevitably be an expectation that
it be current / correct. And that means at least occasionally correcting or
changing it. Even if you have the extremely rare case where the real world
data key is so stable it wil never never never change, (not even
state/province abbreviations are that clean/stable e.g. Quebec due to their
political problems) you still have the problem of making corrections to that
data for wrong entries.
I've also seen plans that try to blend the two. That generate a primary
key from actual data (e.g. a company name) but then never ever change it.
This presents a dilemma. You can't have you cake and eat it too: first,
does it show real world data or not? If the answer is yes, then the data is
wrong as soon as there is a change or a correction. If the answer is no,
then what are you putting real world info into the key.
If the key is abstract, then it is controlled by the database....there are
no hooks that could allow outsides forces to mess with it.
Fred
ony Toews [MVP]" wrote:
M. wrote:
BlueClaw setup for Employee table
BlueClaw are full of cr*p. The middle sentence in the following is
exceedingly arrogant.
"You may look at this design and say you have always seen the
Employee_ID set as the table primary key. No matter what your teacher
or books say - this would be incorrect. See why you almost never use
an artificially generated numeric ID as a primary key."
As far as thier "Table Design Example - Detail Table" goes it's based
on a very faulty assumption. That the employee only does one task
throughout the day. And that's seldom the case.
And they don't explain themselves very well either.
As Jeff states this can become a religious war.
In my opinion use autonumber primary keys on every table and unique
and duplicate indexes as appropriate.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/