Re: Design approaches about primary key

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 27 Nov 2003 16:16:00 -0800
Message-ID: <1069978588.974101@yasure>

Domenic G. wrote:

> Using meaningless keys when meaningful ones exist is stupid because
> you create a condition that always requires a join to pick up the
> value that everyone understands.  Bad design -- it comes from people
> copying textbook examples and using them in real life.
> 
> My social security number is a key, so is my phone number (for a
> telco) -- they don't assign a sequential number on top of that.  Use
> an integer when their is no key, or when the composite key would be
> way way too wide.
> 
> Just my 2 cents .../Dom.
> 
> 
> "Georg Scholz" <georg.scholz_at_vienna.at> wrote in message news:<3fc5d07c$0$19266$91cee783_at_newsreader01.highway.telekom.at>...
> 

>>Dear all,
>>
>>This is a general design question.
>>
>>Over the years, I've been creating an oracle database storing financial
>>data; about 60 tables.
>>I consequently designed all tables this way:
>>
>>- Primary key ALWAYS is an integer, generated by a sequence (separate
>>sequences for each table)
>>- The "logic key" (e.g. a company Product ID) ist stored in an extra field,
>>combined with an unique index
>>- All reference constraints are always made upon the primary integer keys.
>>
>>So far I think this is a common approach. The main advantage is you can
>>change the "logic key", without the need to do any changes in detail tables.
>>
>>However, if you look into the detail tables, you see a bunch of integer
>>numbers which are completely meaniningless unless you join them with their
>>master tables.
>>Also if you want to insert or retrieve data, you always have to lookup or to
>>join with other tables.
>>
>>I currently ask myself if using the integer primary keys is really worth the
>>effort.
>>So what about replacing the integer keys by "meaningful" keys and also
>>storing these keys in the detail tables?
>>I know, if a key needs to be changed, then detail tables need to be
>>changed, too.
>>But on the other side a lot of trouble when handling table data will be
>>avoided.
>>
>>What's your opinion about this topic?
>>Do you have any experiences with databases in these two design apporaches?
>>
>>Best regards
>>
>>Georg Scholz
>>www.scholz-informatik.at

Actually your telco assigns a natural key too. They call it an account number. And the reason they do is because different people, over time, have the same phone number.

One more little bit of knowledge gained from working for Ma Bell.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Thu Nov 27 2003 - 18:16:00 CST