Re: Design approaches about primary key

Georg Scholz wrote:

> 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

I believe, as does Joe Celko, that there are few things worse than the "mindless" creation of surrogate keys (or as he calls them artificial keys). There are times when they are the best and most appropriate solution. But those situations are not as common as many believe. And the price paid includes extra constraints and extra indexes that serve no real purpose.

While this link is in relationship to SQL Server it is worth reading: http://groups.google.com/groups?q=%22Celko%22+AND+%22Surrogate%22+group:comp.databases.ms-sqlserver+group:comp.databases.ms-sqlserver&start=10&hl=en&lr=&ie=UTF-8&group=comp.databases.ms-sqlserver&selm=7e67a7b3.0106191214.3b99d84%40posting.google.com&rnum=11 (reconstruct if the long link breaks into multiple lines)

I think what you are saying is that you have developed a habit ... and a habit that does not truly relate to the data. That would make it a bad habit and I'd suggest you break it.

-- 
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)