Dealing with Addresses: poor data structure?

[EternalMyrtle]

So, I am not sure how I made it this far without dealing with my mailing address issue but I have so please don't judge me

I want the user to be able to select a mailing address for any given contact. The mailing address could either be the contact's "Home" address or his/her "Office" address. The problem is that I have a separate table for Company Address (tblCompaniesLocations). Contacts' home addresses, which are just in the contacts table (tblContacts). Each contact is linked to his or her company through another table (tblContactsCompanies).

It would be easy enough to link each contact to his or her current company address (or obviously to his or her home address) but it is not so easy to link to either the home address in tblContacts OR to the office address is tblCompaniesLocations (or is it).

Is the problem my data structure?

I would appreciate any advice. Thank you!

 

[ButtonMoon]

Here's a good place to start: Party Data Model.

 

[EternalMyrtle]

Thanks, Button Moon, this is a great resource.

So, this is advocating merging my Contacts table with my Companies table to make one big "Party" table.

Needless to say, this would require a major overhaul of my structure. I am willing to do it if it is the best way (or the only way).

Any opinions on this?

 

[EternalMyrtle]

After a lot of consideration, I have decided to go "Party Lite". I am making a meta-table called tblEntities which will look like this:

EntityID (PK)
CompanyNameorLastName
FirstName

This way both companies and contacts will have a unique EntityID number .

tblContacts, tblCompanies will get an EntityID FK field, which will link to the PK of tblEntities. The tblCompaniesLocations (my address table) will need some minor restructuring and the EntityID FK field will need to be added.

I can alter my form queries so they include tblEntities (so that a new EntityID will be created with each new company or contact) and I should be good to go.

This is the plan. We shall see how it goes. Am I on an alright track here?