Re: Sequence Numbers as Primary Keys

drolfe_at_eng (David Rolfe) wrote:

>In article aep_at_dingo.cc.uq.oz.au, gordonh_at_acslink.net.au (Gordon E. Hooker) writes:
>> drolfe_at_eng (David Rolfe) wrote:
>>
>> >Using a sequence to provide a primary key is all very well but creates its own
>> >problems -
 

>> >1. If every table in your system has a numeric primary key and you are using
>> > Sql*Forms you will not be able to make records appear on the screen sorted
>> > by a foreign key value, 'cos the foreign key stored on the table the block is
>> > based on is a meaningless sequence number and the 'real' key is on another table.
>> > The only may round this in forms is to enter the strange and unpleasant world
>> > of ON-SELECT triggers.
>>
>> Ever thought of putting an order by clause on the block.
 

>The default forms ORDER BY behaviour only works on database columns which are included
>in that block. Here's an example:
 

>Let's use the EMP and DEPT tables. Imagine that DEPTNO is a system generated key
>that has no meaning to users. If the application needs to bring back employees
>sorted by employee number you can say
 

>ORDER BY EMPNO
 
>If you need to get the records sorted by *department name* there is no easy way to
>do so - You have to join to the DEPT table to get DEPT.DNAME *before* you can refer
>to it in an ORDER BY. The current version of forms does not allow you to have more
>than 1 base table for a block. Game over.
I'm sorry, but I can't resist, only cuz David seems to reallly know his stuff. David's point, is correct but he's slightly off in that you HAVE to venture to the world of on select, you can also make a view and not mess around at all with the on-select trigger (of course that does bring up the on-lock, on-update.....)
>>
>> >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 don't believe people have said to use system generated keys on all
>> tables. I believe this thread was about concatenated primary keys and
>> performance issues.
I have seen people use this as a MAJOR cop out during design.

>Maybe not in this thread, but I've been unfortunate to work in an environment where
>*everything* has a system generated primary key. The reason I made the post was
>because there are two extremes - no system generated PKs and only system generated
>PKs and either one of these extremes will hurt you.
>>
>> >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.
>>
>> Again use order by an order by clause in the query...
>>
>But I'll have to join to the foreign key table in question before I can to the
>ORDER BY. Even if the FK is not part of the ORDER BY I'll have to join to the FK table
>just to get the information. If a lot of FKs are system generated you end up in a situation where trivial queries have to hit 4 or 5 tables to get the information users want in the order they want it. This has a significant effect on performance.
But in the right cases, these reference tables will speed things up, and not just for C code. Its a fundamental concept that computers are better with numbers than with character strings. You do have to use some common sense though. There are benefits and drawbacks, thats the science. Its the Art that strikes the right balance for a given system. And its the Guess if it was the right choice before you implement it.

>> >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.
>>
>> Refer to the comment against para 2
 

>I may well have been slightly off-topic here. Sorry.
I'd have to say it depends on the documentation and the developer. But if the developer can't figure out a many-many resolution table cuz its all numbers.....

>>
>> >Please don't take the above to mean that system generated keys are a bad thing -
>> >they can solve a lot of problems. But from my experience I have learnt to use them
>> >when I have a genuine need, and not because its fashionable or because some developer
>> >claims it'll make his C program run faster.
>>
>> Test it and see, if performance is enhanced use it...
 

>'performance' could mean many things. If I have to add derived data or some other
>anomoly to improve the speed at which part of the application runs I may end up
>creating maintainence/integrity problems down the road. The cost of solving these
>problems could easily exceed the benefit if having the program run faster. I think
>you are oversimplfying things. Tweaking the design so one program runs quickly could
>also make several others run much more slowly.
This is true, although there are some solutions that make ALL parts run quicker and all parts easier to maintain (unfortunatly its different solutions to only some of the possible problems)

>>
>> >Below are the circumstances I know of that would justify using a system
>> >generated key:
 

>> >1. Unique key is too big (e,g. 4 columns)
 This is a Good example of how to speed up oracle joins.
>>
>> Exactly what this thread was about...
>>
>> >2. Unique key is unstable (business requirement to allow updates).
>>
>> Then is not really a suitable primary key...
This is theory vs practice.

>In the real world designers often have to make do with Unique Keys which are
>*slightly* unstable. American Social Security numbers are a good example.
As I just said, Theory vs Practice :)

>Sometimes you have to accept that the *only* possible unique key may change
>every now and then. You can not go back to the users and tell them to fix the
>unique key of one of their business entities. It may be more cost-effective to
>accept that there is a 1/10000 chance of the unique key changing and write a utility
>to handle the event than to use a sequence number as a PK and then hit problems with
>sorting in Forms applications and gratuitous joins in reports.
 

>>
>> >3. Major storage space problems.
>>
>> Now your talking
>>

Typing.

Craig Received on Thu Oct 05 1995 - 00:00:00 CET