Three Way Table Exchange

[damaker]

So here is the situation:

I have three tables:

Customers

Contacts

Units

Customers table is a parent to both contacts and units.

A contact may or may not have a unit/units associated with them.

A unit will always have one contact associated with them.

I have tried laying this out in a relationship graph and have been unsuccessful thus far. Am I suppose to use a join table or multiple table occurences? I am at a loss of what to do. Any help would be greatly appreciated.

Regards,

DM

[comment]

So a unit belongs to a customer AND is also assigned to one of the customer's contacts?

[damaker]

Correct. But the contact associated with the unit can change.

[comment]

Well, one way to handle this would be for the Units table to have a ContactID foreign key field, in addition to the CustomerID one. On the relationships graph, you will need another occurrence of either Units or Contacts - it depends on how you prefer to do the data entry.

[damaker]

Thank you for your help.

Currently tables are set up like this:

Customer Table: CustomerID

Contacts Table: ContactID CustomerID_fk

Units Table: UnitsID ContactID_fk CustomerID_fk

Additionally I need to be able to input new Contacts and create a Unit Association.

Based on that information, how should I proceed with the second occurence of either the Contacts Table or the Units Table?

Regards,

DM

[comment]

How do you want to "create a Unit Association"? Do you want to do it from a layout of Contact, or from the Unit? Not that it matters very much - it's more a question of convenience. Either way you start with the 2 relationships:

Customers::CustomerID = Contacts::CustomerID

Customers::CustomerID = Units::CustomerID

Then you can add either:

Contacts::ContactID = Units 2::ContactID

or:

Contacts 2::ContactID = Units::ContactID

or even both - though I doubt you'll actually need both.

[damaker]

Thank you for you help. I have implemented both of the secondary table occurrences as I will need to have the flexibility to input assign contacts to Units and Units to contacts.

Out of curiosity, is this the only way to accomplish this? I have read some things on join tables, but wasn't sure how to make that work in this situation. Again thank you for your help.

[comment]

A join table is a device to implement a many-to-many relationship. All your relationships are one-to-many, so there's no need for a join table.

That said, the Units table in your case is technically very similar to a join table between Customers and Contacts. If the only association of a contact with a company were through the assignment of a unit, that's exactly what it would be.

[damaker]

Great. Good to know!