Re: Design approaches about primary key

Hi Georg:

    As you can see from these responses the answer is "it all depends". A couple of points about meaningful primary keys.

1. Can you guarantee the meaning will not change over time? Take a company with fifty offices and an offices table. If office_id is the primary key and you assign STL as the value for the StLouis office, what happens when that office is moved to Milwaukee? The "meaningful" key now is misleading at best.
2. Sometimes you just can't find a meaningful key. Consider a table of persons. What fields, known to you, could you possibly pick to uniquely identify a person? Not surname, not surname + firstname + middle initial. Not social security number or DNA since those are usually unavailable to you.
3. Sometimes you have a problem whether you choose meaningful or meaningless keys. If you had a table of countries a few years back, no matter whether the primary key was a sequence or the United Nations country code, when the Soviet Union split into multiple countries you had a problem.

Van

"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 Fri Nov 28 2003 - 04:16:52 CST