I have a central table, A, which will have a key that I'll call A_ID. Now, I have a table B in a "one to many" relationship with A (many B's map to one A). Each link will, of course, have a link back to A_ID. But now I have a third table C in a "one to many" relationship with B. So, in B, I need at a minimum, a key B_ID so each C can link to the appropriate B. In this way, every A would have a unique A_ID and every B would have to have a unique B_ID.
But maybe a better way would be to let B_ID be unique within all the B's that link to a particular A. Then the B key would be a combination of A_ID and B_ID. And then I would have to make space in C for both A_ID and B_ID so each C could map back to its appropriate B.
I see advantages and disadvantages both ways. The second method requires a bit more storage and duplication. The first way would require more work from the user adding in new data. (There's going to be alot of new data added in.) Any thoughts? Is there a "preferred" way to handle this situation? Thanks.
In both models:
CREATE TABLE A ( A_ID INT NOT NULL IDENTITY PRIMARY KEY )
CREATE TABLE B ( A_ID INT NOT NULL REFERENCES A (A_ID), B_ID INT NOT NULL IDENTITY PRIMARY KEY ) CREATE TABLE C ( -- No direct relationship to A B_ID INT NOT NULL REFERENCES B (B_ID), C_ID INT NOT NULL IDENTITY PRIMARY KEY )
CREATE TABLE B ( A_ID INT NOT NULL REFERENCES A (A_ID), B_ID INT NOT NULL, PRIMARY KEY (A_ID, B_ID) ) CREATE TABLE C ( A_ID INT NOT NULL, B_ID INT NOT NULL, PRIMARY KEY (A_ID, B_ID), -- Note sure about this line -- OP FOREIGN KEY (A_ID, B_ID) REFERENCES B (A_ID, B_ID) )