Re: Design approaches about primary key

>> 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) is stored in an extra field [sic], combined with an unique index; All reference constraints are always made upon the primary integer keys. <<

This is redundant and dangerous. I'll get to the details shortly.

>> So far I think this is a common approach. <<

Bad design often is common :) It is being done by people without any RDBMS training and they desparately want to see sequential record numbers so they don't hav to learn RDBMS. Did you notice that you called a column a field, just as if it were in a file? The differences between files and tables, rows and records, fields and columns are huge and vital.

>> The main advantage is you can change the "logic key", without the
need to do any changes in detail tables. <<

That is what ON UPDATE CASCADE does for you automatically -- if you designed a database instead of a file in SQL. And what the heck are "detail table" and "master table"? I remember such terminology from an old Network database and sequential tape file systems, but NEVER in a relational database.

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

Which you can never verify. Tryt this table:

CREATE Drivers
(driver_id IDENTITY (1,1) NOT NULL PRIMARY KEY,  ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),  vin CHAR(17) NOT NULL REFERENCES Motorpool(vin));

Do your joins on driver_id. Find an error in the ssn (Social Security Number, the tax identification number for US citizens) and correct it in just Drivers. None of your joins know that the driver_id refers to the wrong person now. You have to make sure that the redundant key is also changed and that no application -- present or future -- fails to do so. Double the work at best. Mostly likely have no data integrity.

I find these systems fall apaprt in about a year. Received on Fri Dec 19 2003 - 18:19:14 CST