Thread
-
Re: PRIMARY KEYS
wsheldah@lexmark.com — 2003-05-22T15:43:50Z
Choosing an artificial key is the ideal, according to everything I've heard. In one of my database classes, I remember I had a classmate who had worked with some very large datasets of U.S. citizens, and found that there were actually duplicate social security numbers assigned to different people. Not many, and I don't recall whether the first person had died before the SSN was reused, but it really goes to show that they only to _guarantee_ a unique primary key is to generate it yourself. Yes, you may want to put a unique index on your SSN field or other candidate key fields that ought to be unique. Integer keys are also faster to compare and sort on, so I would expect joins between tables to execute faster if the join fields are single integers, compared to a PK that is a combination of varchar() fields. Wes Sheldahl elein <elein@varlena.com>@postgresql.org on 05/21/2003 09:03:09 PM Please respond to elein@varlena.com Sent by: pgsql-general-owner@postgresql.org To: Karsten Hilbert <Karsten.Hilbert@gmx.net>, pgsql-general@postgresql.org cc: elein@varlena.com Subject: Re: [GENERAL] PRIMARY KEYS This is unlike any database theory I've heard of. Choosing a natural key over an artificial key is the ideal. I've heard that a lot. Sometimes there are several candidate keys to choose from. And sometimes the primary keys are more than one column. Sometimes I bail out to an artificial key when the primary key is too long, but it depends very much on how the table will be accessed and who knows what and when. --elein On Tuesday 20 May 2003 05:41, Karsten Hilbert wrote: > And - if you agree with database theory - a bad one at that. > Supposedly primary keys should be void of any meaning bar > their primary key-ness. I got into the habit of starting > any but the most simple table like this: > > create table ( > id serial primary key, > ... > > Never had any trouble with that. Good or bad practice ? Gotta > decide for yourself. > > Karsten > -- > GPG key ID E4071346 @ wwwkeys.pgp.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > -- ============================================================= elein@varlena.com Database Consulting www.varlena.com PostgreSQL General Bits http:/www.varlena.com/GeneralBits/ "Free your mind the rest will follow" -- en vogue ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly -
Re: PRIMARY KEYS
Mike Mascari <mascarm@mascari.com> — 2003-05-22T18:33:18Z
wsheldah@lexmark.com wrote: > Choosing an artificial key is the ideal, according to everything I've > heard. In one of my database classes, I remember I had a classmate who had > worked with some very large datasets of U.S. citizens, and found that there > were actually duplicate social security numbers assigned to different > people. Not many, and I don't recall whether the first person had died > before the SSN was reused, but it really goes to show that they only to > _guarantee_ a unique primary key is to generate it yourself. Yes, you may > want to put a unique index on your SSN field or other candidate key fields > that ought to be unique. I think the desire to have an artificial numeric key is founded in the manner in which SQL has implemented the relational model. Logically, artificial candidate keys have no business in a relation. But I agree in their convenience in throwing around keys of a consistent size and type in client applications and middleware. So, IMHO, I think the modeler should first design the database to be *logically consistent*: 1) Each relation has a unique, natural candidate key (the x of the relation) - relations are sets, not bags. 2) Each relation's non-key attributes (the f(x), g(x), ... of the relation) should be dependent upon the natural key, the whole key, and nothing but the natural key - that's 3NF at a minimum. Then, once the model complies with the RM wrt constraints on the domains, relations, and a database as a whole, one could go back and add the artificial keys for convenience purposes. It's the modeller's job to design a database that ensures logical consistency *first* in the face of a users, programmers, dbas, etc. that will attempt to break it. The database should be, logically speaking, unbreakable. But the whole point of the RM is that it is *provably* logically consistent if its prescriptions and proscriptions are followed. SQL doesn't force that on you, which is probably a mistake... IMHO, Mike Mascari mascarm@mascari.com
-
Re: PRIMARY KEYS
Mark Wilson <mwilson13@cox.net> — 2003-05-22T20:50:37Z
On Thursday, May 22, 2003, at 11:43 AM, wsheldah@lexmark.com wrote: > > Choosing an artificial key is the ideal, according to everything I've > heard. In one of my database classes, I remember I had a classmate who > had > worked with some very large datasets of U.S. citizens, and found that > there > were actually duplicate social security numbers assigned to different > people. Not many, and I don't recall whether the first person had died > before the SSN was reused, but it really goes to show that they only to > _guarantee_ a unique primary key is to generate it yourself. Yes, you > may > want to put a unique index on your SSN field or other candidate key > fields > that ought to be unique. > > [snip] Wouldn't one wish to know, and deal with, a situation where a business rule -- each person has a unique social security number -- has been violated? If you're a business doing tax withholding for employees, isn't this a critical question?