Re: Design approaches about primary key

Hi Georg,

Artificial ("meaningless") keys have their place and should be used in implementation when appropriate, but certainly not unconsciously.

General rules of thumb :

1. When the "real" key is not a composite key, artificial keys are rarely (but not always) justified.
2. In a stand-alone table (one with no children and parent tables), artificial keys should not be used.
3. The more fields the real key spans over (i.e the more "composite" it is), the more meaningful an artificial key will be.
4. The more often a composite key will appear in children tables and even grand-children tables, the more meaningful an artificial key will be. It obviously costs less space to propagate an integer artificial key down in the children/grandchildren/grand-grand children tables. Quries become easier too.

I think a complete and correct datamodel is essential for you to make the right implementation choices regarding artificial keys.

I've seen a lot of systems suffering from awkward composite keys appearing everywhere, mainly because those system had been growing over time, but I've never (before) seen a system suffering from too many artificial keys as yours do.

• Kenneth Koenraadt

On Thu, 27 Nov 2003 11:23:19 +0100, "Georg Scholz" <georg.scholz_at_vienna.at> 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
>
>
Received on Thu Nov 27 2003 - 14:39:48 CST