I have a table that has a composite primary key. It is made up of four fields. I am creating a second table that will track additional information about the primary key. The relationship between the two tables is a one-to-many. My question is this, is it better to create a separate field in the one table (AutoNumber) and use this in the many table. Or, do I duplicate the structure in the many table each time I add a record. I am interested to know the pros and cons of each approach.
I personally am leaning toward a second field (Autonumber) so I don't have to add four fields each time I add a field. But somehow I feel like this might be poor design (although I can't find fault with it right now).
I spotted this and I know it's an oldie but... couldn't resist
Surrogate Keys Vs Natural Keys is a long standing debate with fanatics on both sides. I don't think it is cut and dried either way and there are heavy weights on both sides of the fence for this one. There are those that even argue that surrogate keys violate normal form (someone on this baord pointed that out and I didn't realise quite what the implications were at first).
Relational design is about not duplicating data from table to table.... apart from primary and foreign keys where it is a necessity. Surrogate keys are a purely physical implementation - they shouldn't really ever appear in a logical design. As far as performance is concerned... yes - a single integer primary to foreign key link is more efficient than a composite of text keys. However, my experience is you need to include many more tables in your queries (especially management summary "how many" and "what cost" type queries) because there is no longer any information in the join.