Database design question

[ProfessorFate]

What is the best way to deal with situations where you have a table (TableA) with a primary key with say 4 fields in it, which has a relationship with another table (TableB) so that TableB has a foreign key consisting of the 4 key fields in TableA?

Is it a good idea to introduce a "rowid" here and use that instead (because it's much more convenient and probably more efficient) and how would you use it, or is this bad practice?

Any thoughts would be much appreciated

 

[ProfessorFate]

Thankyou all for your replies

To clarify, TableA is a table with a primary key made up of 4 of its fields (it could have other fields too but these aren't part of its key),
and there is a one-to-one relationship between TableA and TableB.

It was more of a general theoretical question really. I've always been inclined to use artifical record IDs in this case, but I've been told this was a bad thing to do (by my databases lecturer no less!).

Having done a bit of Googling, I think the reason I was told this was to do with making sure I get a properly normalised set of tables with referential integrity and constraints based on the "real" data. Having first done that, then I could look at introducing artificial keys (surrogate keys) to make implementation easier.

So looking at Mr^B's example with his "Person" table in which he replaces a composite key with an artifical key; the composite key fields still remain as a unique index in the table (to prevent duplicates), but the artifical key becomes the primary key which would be used in queries, and as a foreign key in related tables. Am I close?

 

[ProfessorFate]

Having worked with a database (not designed by me I should add!) where all tables used generated keys exclusively with no other indices at all (it was horrid), I think I see your point JIMA.

One article I read suggested having both, i.e; keeping the composite key as a unique index, but adding a system generated field as well, and making this the primary key. I think this is what others have suggested in their replies too. Some articles I read elsewhere even suggested having a surrogate primary key for all tables, and some said they are always bad.

I guess I need more practice and experience so I can form my own opinions, but it definitely helps having experienced people to bounce my ideas off on the forums