Surrogate Keys As Part Of Composite Keys?
Date: 2000/03/26
Message-ID: <38DDF993.3D111DEC_at_207.87.184.178>#1/1
If I have two tables, A and B, with a one-to-many relationship between A and B, so that table B has as part of its data a foreign key from A, and I use a surrogate key for the records in B, then that surrogate key is unique (e.g., the autonumber type field in Access). That means that it can serve as a primary key alone. On the other hand, if it weren't for the fact that a surrogate key existed in table B, the primary key for table B would have to be composite. So my question is, what is the proper way to model table B, 1) use a surrogate key that is unique across the table, in which case it is the primary key, or 2) use a surrogate key that is unique only across the group of records associated with a specific foreign key, in which case the primary key must be the composite key consisting of the surrogate key and the foreign key?
-- % Randy Yates % "Midnight, on the water... %% DIGITAL SOUND LABS % I saw... the ocean's daughter." %%% Digital Audio Sig. Proc. % 'Can't Get It Out Of My Head' %%%% <yates_at_ieee.org> % *El Dorado*, Electric Light Orchestra http://207.87.184.178/index.htmReceived on Sun Mar 26 2000 - 00:00:00 CET