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.

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.

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

The standard way to do this is by creating an "entity" table. An entity can be a company or an individual. The entity table includes the EntityID and any common fields. Two 1-1 tables are also defined. One to hold company specific attributes and the other to hold individual attributes. This solves the relationship problem because the addresses are related to entities rather than to companies or people.

tblEntity:
EntityID (autonumber primary key)
EntityType (company or person)
etc.
tblCompany:
EntityID (pk, foreign key to tblEntity)
etc.
tblStaff:
EntityID (pk, foreign key to tblEntity)
EmployeeID (foreign key to ....)
etc.
tblAddress:
AddressID (autonumber priimary key)
etc.
tblEntityAddress:
AddressID (pk fld1, foreign key to tblAddress)
EntityID (pk fld2, foreign key to tblEntity)
RoleID (pk fld3, foreign key to tblRole)
tblRole:
RoleID (autonumber primary key)
RoleName (billing, shipping, home, etc)

Pat

Superb response, thanks very much