Design approaches about primary key

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 - 04:23:19 CST