> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Dann Corbit > Sent: Wednesday, January 18, 2006 4:04 PM > To: josh@agliodbs.com; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Surrogate keys (Was: enums) > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > > [EMAIL PROTECTED] On Behalf Of Josh Berkus > > Sent: Wednesday, January 18, 2006 3:59 PM > > To: pgsql-hackers@postgresql.org > > Subject: Re: [HACKERS] Surrogate keys (Was: enums) > > > > Martjin, > > > > > Interesting. However, in my experience very few things have "natural > > > keys". There are no combination of attributes for people, phone > calls > > > or even real events that make useful natural keys. > > > > I certainly hope that I never have to pick up one of your projects. > A > > table without a natural key is a data management disaster. Without a > > key, it's not data, it's garbage. > > I have a different opinion. > > The data should absolutely never use a natural key as a primary key. > > The data should use something like a sequence for the primary key. > > Examples: > SSN --> believe it or not, SSN's sometimes change. > > First, Middle, Last names --> Not really unique > > Street Address --> More than one person can live there. They can move. > > Basically, every physical attribute or logical attribute is a terrible > choice for a primary key. They won't cause problems very often, it's > true. But when they do cause problems, it is a terrible doozie of a > problem. > > Now, on the other hand, if we are talking about INDEXES here, that's a > horse of a different color. Lots of natural attributes and > combinations of natural attributes make excellent candidates for keys.
Make that: "combinations of natural attributes make excellent candidates for indexes." See. I even messed it up, when I was trying to highlight the distinction. Of course, we can probably just chalk that up to "dumb as a box of hammers." > Such things as SSN, names, addresses, phone numbers, etc. > > Therefore, I am guessing the two posters upstream in this thread that I > am responding to were therefore talking about different subjects > altogether. > > One was talking about using natural attributes for indexes, which is a > superior idea that I agree with. > > The other was talking about never using natural attributes for keys, > which I also agree with. > > Therefore, I am guessing that everyone is in complete agreement, but it > is a nomenclature thing. > > Just a guess. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
