Re: Sequence Numbers as Primary Keys

In article <447pc8$f9u_at_zippy.cais.net> randyd_at_cais.com (Randy Dewoolfson) writes:


>^^^^^^^ (jhagans_at_telerama.lm.com) wrote:

>: Recently at work ,there has been a controversy between the application development 

>: group (IT) and the database administration group (DBA) concerning using sequence 

Randy! Don't you know this is a way to get a bigger box? Say "look, this box is too slow! Buy us a bigger box!" THEN do proper performance tuning... :)

>
>: The IT group claims that their C++ program will be much more efficient with fixed-length
>: unique identifiers.

This cracked me up, because I'm reading it on an 80 column terminal, so it came out "feh unique identifiers." Changes the meaning, eh?

>:
>: The DBA group refuses to use sequence numbers claiming that they are not "business
>: data" and do not belong in the database.

Tell your DBA group to review the difference between physical and logical design. Of course, the logical design should be completely normalized and not have any non-business data. The physical design has to include reality. Reality means having to denormalize for performance, since performance is not part of relational theory. Part of denormalization may be duplicating data within tables, adding sequence numbers, or even using (gasp!) arrays. Your programming procedures include denormalization documentation... right?

>
>Articles and books by Mr Date are a good start, but may go way over the
>heads of the people you are debating with. (Happened to me) So the best

Really, it does sound like the DBA's aren't all that experienced if they are parroting some theory class. Or maybe they're former COBOL programmers who take the new paradigms much too seriously...

>thing to do (if you can) is to actually test performane on a single key
>table and multiple key table on your system. Each time I do this,
>I find that the single key design is MUCH faster, and to me, MUCH easier
>to maintain. The standard argument is that you can't understand that data
>in the table so it makes it harder... Well, personally, I love to look at
>intersection tables that contain nothing but dates and sequenced keys and
>say to myself 'This means shit...'

Yeah.

>
>:
>: The database has probably around 50 tables with an estimated 6 million hits a day;
>: However it is only a few tables (7 or less) that will be getting the majority of the hits. The
>: composite key in these tables is quite large, usually 5 or 6 columns to make the row
>: unique.
>:
>: We are running Oracle 7 on a RS/6000 Unix box.
>:
>: Any insight to this matter would be greatly appreciated.
>:

Hey, just do the critical tables both ways, show management the numbers, let them decide. Do it full scale, maybe you'll learn the difference is acceptable if you have a few hundred Meg of SGA. The only way to find out is to try.

>: Joel Hagans
>: jhagans_at_telerama.lm.com
>:
>
>Don't give up the fight! You know you're right....
>Randy :)

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O