Re: Sequence Numbers as Primary Keys

In article 9821_at_olmsys.demon.co.uk, pevans_at_olmsys.demon.co.uk (Peter Evans) writes:
>
> >2. Using a system generated primary key can be a cop out. Every table *must* have a
> > unique key which is based on its contents. If you don't know what the unique key
> > is then you can't honestly say what the table is keeping track of. Any damn fool
> > can make something unique by tacking a sequence number on to the front of it and
> > saying 'look! it's unique'. Any table which has a system generated key must also
> > have a 'application related' unique key.
>
> I wouldn't totally agree with this. "Real World" primary keys are
> often not all that common. Simple example is a person's name. Thats
> mostly unique and identifies them 99% of the time but not all the
> time. So, name and address? no, they might have called children the
> same name. Name Address DOB? no, twins etc. etc. etc. In this example
> you have to give a person a reference number as there's no real unique
> way to identify a person 100% of the time.
>

I agree 100%. My point is that system generated primary keys are a like a scalpel in the hands of a wise man and a chainsaw in the hands of a fool. A table *must* have a unique key which is not plucked out of the air by a sequence generator, even if it's imperfect.

I say this because I have seen several cases where the *only* unique key of a table is a sequence number. The designer didn't bother to figure out what the 'real' unique key was and what the problems were with it before they stuck a column called SEQNO at the front of the table. Until you have a candidate unique key you don't *really* know what the table (or entity) is.

> Another example (which we come across with about 30% of our tables) is
> start date - end date primary keys. The primary key may be (for
> example) person's name, classification, and a period (start date - end
> date). The end date is often null and thus can't be used as a primary
> key. as it contravenes Codd the God's rule about PK's

I get round this by having a system wide constant called C_MAX_END_DATE or something like that. It's set to 01-jan-4000. End dates are always not null and the constant is used when the end date is open-ended. This works very well in practice as Oracle allows you pick a date which is so far in the future that *nobody* could interpret it as anything other than meaning that the relationship in question is open ended. It also means that you can use indexes properly, which you can't if you have to use NVL on your end date column to make sure it comes back.

While we're on the subject I wish oracle would introduce a DATE RANGE data type. Having a start/end date pair in your key is all very well but you have to write additional code to watch for overlaps

>
> >3. If everything in the system has a numeric primary key your reports will end up
> > doing large numbers of joins to 'decode' foreign keys into something meaningfull
> > to humans.
 

> >4. Systems in which everything has a numeric primary key are utterly incomprehensible
> > to new developers. Instead of being able to see vaguly familer business terms all
> > they have are lots of tables, some of which will consist of nothing but numbers.
>
> Yeah, this is a pain when maintaining other people's systems, but it
> does make for fast joins.

Fast joins are fast but no joins are better. If all you wanted was to decode the foreign key into something meaningfull so you could list it on your report you might not have to do the join in the first place if the PK wasn't a sequence number.

>

[ uncontroversial stuff removed ]

>
> Interesting discussion, nice to see somebody supporting the "no
> sequence nubmers" point of view.
>

I don't support the "no sequence numbers" point of view or the "only sequence numbers" point of view. I'm trying to make the point that both extremes cause problems and that the proper course is a some of both. I'm also concerned that people use system generated numeric primary keys as a substitute for good analysis.

David Rolfe,
SunSoft,
California. Received on Wed Oct 04 1995 - 00:00:00 CET