Supertype/Subtype

[frinholp]

I am creating tables using the supertype/subtype model. I am wondering what is the standard in terms of which key field I should be using to create the relationships.

I have a contacts table which has a key field ContactID. A contact could be a supplier, a customer or both. If a contact is a customer a CustomerID is automatically generated and held in the contacts table. Also if a contact is a supplier a SupplierID is generated in the contacts table. I then have two tables holding details which are specific to customers and suppliers. Which key should I be using to create a relationship between the tables. Should I be using (1) the ContactID or (2) either the SupplierID or CustomerID.

Thanks in advance

Lee

Edited May 27, 2010 by Guest