Address table structure

[RobertG]

Hi all,

I hope this will be a simple question/answer, so I'll explain in brief.

I have a need to track address(es) for people in a database, as well as the
address(es) of their employer. How would I do this with only one address
table?

Here is what I'm thinking (pertinant info only):

1 Person -> Many Employers
1 Person -> Many Addresses
1 Employer -> Many Addresses

tblPerson
PersonID (PK)
Other Info...

tblEmployer
EmployerID (PK)
PersonID (FK)
Other Info...

tblAddress
AddressID (PK)
PersonID (FK)
EmployerID (FK)
Other Info...

The address table is the one that I'm second-guessing myself on... it looks
a bit odd to me having both the PersonID and EmployerID as FK in there. Is
this the correct way of doing this or am I way off? If this is not the
correct way to do something like this, any suggestions/critique will be
greatly appreciated!

Thanks,

Robert

 

[Douglas J. Steele]

In actual fact, it's more likely that it's a many-to-many between Person and
Address. You're saying a person can have many addresses, but many people can
live at the same address. It's likely also a many-to-many between Employer
and Address, especially if you're tracking addresses over time.

That means that you need additional entities to resolve the many-to-many
relationships, like a PersonAddress table (containing PersonID and
AddressID) and an EmployerAddress table (containing EmployerID and
AddressID)

 

[Paul Shapiro]

You might consider consolidating Persons and Employers into a single Party
table, with subcategory tables for Person and Employer. Then you can have a
single PartyAddress table with a 1:M relationship from Party. Or if you want
to allow multiple Parties at the same Address, then PartyAddress would be an
association table with a 1:M relationship from Party and another 1:M
relationship from Address.