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

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

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

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.

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

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.

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.

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.

Great. Good to know!