Multiple one-to-one relationships

[tuna]

I have a contacts table, tblContacts, containing info on people and companies (e.g. Northwind corp.). However, I plan to create two new tables, tblContactsPersons and tblContactsCompanies, to contain the data that isn't relevant to all contacts (for example, company contacts do not require a "LastName" field). These tables would then be linked by one-to-one relationships back to tblContacts by a contactID field.

However, how can I ensure that a single contactID is unique across tblContactsPersons and tblContactsCompanies? That is, how can I ensure that a single contact doesn't get logged as both a person and a company?

Thanks

 

[FoFa]

I would keep all the data in a single table (basically) with a TYPE indicator to tell me what it is.
I.E. Type = P, it is a person
Type = C, it is a company

You can then handle it how you want.

 

[tuna]

Thanks, but then how can I avoid lots of redundant fields that apply to people but not companies, vice/versa - e.g. LastName, OtherNames, D.O.B, MobileNumber, HomeNumber, Ethnicity etc

 

[FoFa]

Create a fields that apply to both. Say use Last name for Company name when looking at a company record.
You can even create two forms, one for companies, and one for people, and filter on your type field. Then you won't need to manipulate the field labels even.