Re: An improved technique for database primary keys

Would the title, and the tone of your post, would have been "An alternate technique ..." instead of "An improved technique ...", I would have had a completely different feeling.

I really abhor your thoughts now. This goes straight into colision course with normalization. There is absoloutely no need for surrogate keys if the primary key is unique and used as such. In many many many occasions, the real key indead has a meaning, or even better, a centralized (or decentralized looking from the opposite site) location where the "base" table values can be fetched for reference. Think of the prefix for phone numbers for countries. In those cases the keys are obvious and logical. Dialing +31 will get you to the Netherlands, and that is very unlikely to change. In a table that would store the country name for prefixes, the keys should simple be 31.

Enjoy, Have FUN! H.Merijn

Comment on Re: An improved technique for database primary keys Download Code

Replies are listed 'Best First'.
Re^2: An improved technique for database primary keys by locked_user sundialsvc4 (Abbot) on Nov 05, 2010 at 17:47 UTC
Poof... your wish is my command. I have no wish to be provocative in my selection of titles, nor in the tone of my posts. (Thank you for the guidance.) I changed the thread-title, although the child-records (existing replies) were not changed thereby. Nodding politely (but, flinching slightly) at your use of the word, “abhor,” I find it to very often be the case that no “human provided” number serves well as a primary-key. As the subject of a “`UNIQUE`-indexed column,” perhaps, but not as “the glue that sticks everything together.” I say this because “unique numbers” in the human world are often not perfectly unique, or if they are, do not stay that way. And when (not if...) one of these eventualities happen, they are very problematic for the computer. An account-number change, for instance, that “ripples” through thousands of records, for instance, and/or that invalidates an archive if the account is of very long standing. An assigned number (in-use by a human) that cannot be stored. Or, as I related above, a number that does not have the one-to-one correspondence with reality that it should (according to the good Dr. Codd) have. In those situations, surrogate (or “synthetic”) keys might excel. You are obliged to handle, and to continue to handle, a real-world circumstance that is “mathematically speaking, compromised.” The business isn’t going to stop for a schema. I dealt with that first-hand, and yes, it shaped my thoughts. Of course it is a choice to be made; not an absolute maxim. “So help me, Codd.”

Replies are listed 'Best First'.

Re^2: An improved technique for database primary keys
by locked_user sundialsvc4 (Abbot) on Nov 05, 2010 at 17:47 UTC

Poof... your wish is my command. I have no wish to be provocative in my selection of titles, nor in the tone of my posts. (Thank you for the guidance.) I changed the thread-title, although the child-records (existing replies) were not changed thereby.

Nodding politely (but, flinching slightly) at your use of the word, “abhor,” I find it to very often be the case that no “human provided” number serves well as a primary-key. As the subject of a “UNIQUE-indexed column,” perhaps, but not as “the glue that sticks everything together.”

I say this because “unique numbers” in the human world are often not perfectly unique, or if they are, do not stay that way. And when (not if...) one of these eventualities happen, they are very problematic for the computer. An account-number change, for instance, that “ripples” through thousands of records, for instance, and/or that invalidates an archive if the account is of very long standing. An assigned number (in-use by a human) that cannot be stored. Or, as I related above, a number that does not have the one-to-one correspondence with reality that it should (according to the good Dr. Codd) have. In those situations, surrogate (or “synthetic”) keys might excel. You are obliged to handle, and to continue to handle, a real-world circumstance that is “mathematically speaking, compromised.” The business isn’t going to stop for a schema. I dealt with that first-hand, and yes, it shaped my thoughts.

Of course it is a choice to be made; not an absolute maxim. “So help me, Codd.”