Table Lookup Primary Keys

[SteveAlb]

An Access app has a table named tlkpReason that looks up
values for a table named tblReasonperCase that is linked
to another table named tblInquires.
The tblReasonperCase table has two joined primary keys
that are allows duplicates.
What does dual primary keys accomplish? Is this a common
practice in table design and configuration?

 

[Tim Ferguson]

An Access app has a table named tlkpReason that looks up
values for a table named tblReasonperCase that is linked
to another table named tblInquires.

Okay: a many-many relationship between Reasons and Inquiries. Makes sense.

The tblReasonperCase table has two joined primary keys
that are allows duplicates.

Umm no: what it should be is ONE primary key made up of TWO fields. And you
can't have duplicates because otherwise it couldn't be a key.

Of course, the two foreign key fields can (must?) allow duplicates.

Is this a common
practice in table design and configuration?

Yes. The ReasonsPerCase contains the details of each Reason cited in each
Inquiry (probably should really be ReasonsPerInquiry for consistency). This
is a bog-standard approach and is documented in any primer on R theory.

Hope that helps


Tim F

 

[DDM]

Steve, a primary key field serves as a unique identifier for the records in
a table. In the case of your tblReasonperCase table, no single field can
serve this purpose (as you point out, both the fields that make up the
multi-field key allow duplicates). What does add up to a unique identifier
is the values of the two fields taken together, and that is why the two of
them together serve as the primary key.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com