Re: Design approaches about primary key

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
Received on Thu Nov 27 2003 - 16:00:58 CST