Relationships

[captain stag]

I'm trying to create a db to store for example Company Details, Staff Details, and Address Details.
Each company may have multiple addresses
Each staff may have multiple addresses
Each Address is EITHER a company/staff address
The address table structure is the same for both Company & Staff

I have come up with three solutions, each of which does not seem correct.

1) Create two Address tables each linked one to many from the Company/Staff
PRB: Duplicate address tables seem inefficient and queries/future modifications will need to be duplicated

2) "Tag" the address table either by adding an additional field specifying if Company/Staff, or programatically manipulate the key (+ve = company; -ve=staff)
PRB: Lose referential integrity: extar manipulation of queries, code etc.

3) Create a Junction Table with fields for the Address Key, Company Key, and Staff Key.
PRB: Not really a many-to-many (as Each Address relates to one of Company/Staff); Not normalised as either the Company or Staff Key will be empty

I'm sure I must be missing the obvious, but any suggestions would be greatly appreciated.

 

[databasedonr]

I'd suggest just one address table, with an AddressID, AddrType (either company or staff) and a Foreign key that could be used for Company/Staff Primary Key.

In fact, I might go one step further and suggest just one table for all your contacts and distinguish them by contact type (either Company or Staff). That way you're dealing with one Primary Key, One Foreign Key -- unless you're dealing with really substantially different information for comanies versus staff -- it doesn't sound like it from your example -- but I don't think I have enough information to really judge.

If you want to keep two tables for Staff and Company, you could create two foreign key fields in the address table, allow nulls in the field, and link the company or staff appropriately.

Hope this helps.

 

[captain stag]

Thanks for the reply.

You're right, I do need to keep the company and staff as separate tables (massively different structure/fields).

Your final point with two foreign keys will achieve the neccessary functionality (referential integrity etc), but one of the foreign keys will always be null.

It is however a little "messy" as it breaks one of the principles of normalisation (to avoid "null" fields).

I was just hoping that there might be another approach

Thanks again

 

[captain stag]

Pat

Superb response, thanks very much