Question Cannot work out entity relationships...

[Chrisopia]

Three entities: Customers, Invoices and Contacts.

Basically a customer (e.g. a company) would have a main contact for marketing purposes, but individual invoices for that company could have many contacts.

I have gone around in loops, believing this is a three way relationship... which I implemented and came up with a load of errors and issues...

[ Customers ]--- < [ Invoices ] > ---- [ Contact ] > --- [ Customer ]

I just can't work out how to simplify this and make it work?

 

[jdraw]

I don't follow why the Marketing Contact at a Customer(Company) should be related to an Invoice.

Please see the following models

Customer - Contact
http://www.databaseanswers.org/data_models/contact_management/index.htm

Customer - Invoice
http://www.databaseanswers.org/data_models/customers_and_invoices/index.htm

 

[Chrisopia]

I see, those are both very useful diagrams.

I was getting mixed up because I am amending a database which previously only had

[ Customer ]----< [ Invoice ]

In which the Invoice table has an attributes for ContactTitle, ContactName, ContactSurname...

After a few invoices I found I was entering the same information, e.g. Company A would have 3 invoices from Joe Smith, and 2 from Mary Smith... I was trying to invent the third entity so I can choose whether Joe or Mary would be paying for the next invoice (probably from a simple drop down box)

So are you suggesting I should try:

[ Customer ]---- < [ Contact ]---- < [ Invoice ]

This did cross my mind but it would cause issues with linking a customer to an invoice... to see a summary of all the companies invoices in one go (hence the third link)

 

[DCrake]

One company can have many contacts
One company can have many invoices

one invoice can only have one contact

 

[Chrisopia]

One company can have many contacts
One company can have many invoices

one invoice can only have one contact

ah - so it is a three way relationship!

The contacts belong to the Company - not the invoices! Of course!!

Therefore one invoice has only one contact!

This makes so much more sense now! Breakthrough!!

 

[Chrisopia]

I tried and tested it but it doesnt seem to work...

A contact can have many Invoices, otherwise I'll be repeating contacts for every new invoice with the same contact...

 

[DCrake]

Yes one contact can have many invoices, but when viewing invoices you should only see one contact for each invoice. It all depends on the route you are taking.

Find all invoices for one company
Find all invoices for one contact

 

[Chrisopia]

Yes I completely agree... thats where I keep getting confused and running in circles.

But looking at those diagrams, I think I sussed it, by adding an interlinking table "tblOrders"

This will hold a CustomerID, the invoiceID and the ContactID,
the contactID will still be linked to CustomerID.
Invoice will link to Order...

It sounds messy now... but I'm just playing with a few things to test it. It makes sense to me. I'll keep in touch...