Database design relationship problem

[cysklement]

Hi, I am facing a database relationship issue. I am not sure if my design is correct or wrong...
My company is leasing alot of machines to customer and I come out with a design like these.
The interpretation is:



A model can have many machine s/n.
each machine s/n must comes from a model.
a machine can only be leased by one customer.
a customer can leased more than one machine.
a customer can make many complaint of a machine
a complaint must comes from a customer...

The problem that I need advice is:
does the complainttable link to to the customer or the machines?
is my machine to customer relationship is correct?

[JeffChr]

i would link the complaint table to both the customer table and the machines table since you have both foreign keys in your complaint table.

[John_G]

I think the complaint table should be linked to the customer - it's the customer that initiates the complaint, as long as the Asset # is in there as a FK, that's OK.

Your Customer - Complaint relationship is wrong, though. It is not many-to-many, but one-to-many, Customer to Complaint. If a customer complains about two machines at the same time, just enter it as two separate complaints. Even though a customer can complain about the same machine many times, that is a business situation, and is not a relationship in the database. Each complaint refers to only one machine.

Note that the structure you show above (which may be simplified?) does not allow for historical data. It cannot, for example, tell you which customers have leased each machine in the past, nor can it tell you all the machines any one customer has leased in the past.

[cysklement]

Dear John G,

thanks for your advice.

I have updated my diagram....I was wondering if this solve the problem?thanks

[John_G]

Looks a lot better - solves the many-to-many customer-machine issue.

You don't need separate complaintID and customercomplaint tables. The relationship between customer and complaint is one-to-many, not many-to-many
(one customer can have many compaints, but a complaint can be for only one customer). I don't know the nature of your business, but is it possible that one complaint could be looked after by more than one technician? If so, you will need another join table in between.

Otherwise, it's looking good.

John