Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-08-08 : 08:48:11
|
| Scott Reber writes "Hi Folks,I really like your website and your straight forward approach. However, I saw some database design advice that I see everywhere: "Primary keys should be non-intelligent; that is, their values should be assigned arbitrarily without any hidden meaning.".I strongly agree with this blanket advice for transaction tables (ex: OrderID, InvoiceID), but I question it's value on lookup tables (ex: StateID, StatusID) and master tables (ContactID, ProjectID); especially in small databases.I frequently see developers go to great lengths to follow this advice and hide primary key vales from users in order to "simplify" their application. However, like normalization, I think that going too far adds unnecessary complexity. I often struggle with this question while designing databases. I SEE THE BENEFITS OF MEANINGLESS KEYS AS:-never need to change them or cascade updates-increased performance-doesn't require a full keyboard-faster dataentry with fewer typo's-external references never break (ex: email)I SEE THE BENEFITS OF MEANINGFUL KEYS AS:-easy to remember-easy to read-simpler data models-interfaces are less complex-interfaces require less coding-interfaces require less screen real estate-reports require less paper real estate-tables have fewer columns-queries have fewer joins-users make fewer mistakes confusing records-raw data is wysiwyg-exported data requires less translationCan you think of any benefits I missed? I think there is a strong case to be made for meaningful keys, especially in smaller databases. Triggers and cascading updates make it easy to change meaningful keys when necessary. I think the advice to use meaningless keys should be more of a judgment call and less of a rule. Would you number the US States 1-50 in a StateID field? If this advice was followed on the internet, web addresses would look like this "4567890" and email addresses would look like this "123@4567890".Sincerely-Scott Reber" |
|
|
|
|
|
Meaningless Primary Keys