Entity Relationship Model – double one to many

database-designerd

ERD

erd

A customer submits one or many leads.
A lead belongs to one customer.
A lead is a request or an inquiry.
A lead belongs to one manufacturer.
A manufacturer has one or many leads.

Tables

Only focusing on manufacturer and lead I set up the tables like below.

manufacturer            lead
--------------          ---------------
MId                     LId
name                    MId
description             date
...                     ...

Adding customer table I add CustomerId to lead table.

manufacturer            lead                customer
--------------          ---------------     ---------------
MId                     LId                 CId
name                    MId                 phone
description             CId                 ...
...                     date   
                        ...

Issue:

With all three entity types, is there a benefit to have an additional linking table instead adding ManufacturerId(MId) and CustomerId(CId) as column to Lead table? Does this offend any best practice? Or should I even include two separated linking tables? Thank you for Explanation and 'further reading links'.

Best Answer

Your LEAD table is an intersection between MANUFACTURER and CUSTOMER. That is a perfectly legitimate design. Intersection tables don't have to be pure intersections. They can have other attributes in their own right (such as your date or type).

Related Solutions

Combined or separated tables: File and Message

I would definitely go with two separate tables.

customer wants to communicate by writing a message without having attachment (FilePath) or customer uploads any files without having any message (Content) written.

But with the way you're modeling it right now, there really isn't any relationship between file and message, as they both are dependent on Customer_ID. If you tried to query all files and messages for each customer, you wouldn't be able to tell when the two are related (except maybe by looking at the date).

And it sounds like a customer can have a file without a message (and vice versa). I think it makes more sense to create a "Communication" table (for lack of a better word). Essentially it allows you to keep track of all types of communication (message and file) for your customers, but also ties message with file (attachments) when appropriate.

table: Communication
communication_id   int
customer_id        int
created            datetime

That would allow you to further normalize by taking "created" and "customer_id" out of the file and message tables. Likewise, you'd want to add "communication_id" to both File and Message. Then to report on them, you could do something like this:

SELECT *
FROM Customer cu
INNER JOIN Communication co ON cu.customer_id = co.customer_id
LEFT JOIN File f ON f.communication_id = co.communication_id
LEFT JOIN Message m ON m.communication_id = co.communication_id
ORDER BY cu.customer_id, co.communication_id

Entity relationship many to many

It's not strictly necessary to have an association class to model a M:M relationship. You can just have classes with references to the two parents that would map directly to a link entity. Semantically, an association class would limit you to one relationship between two entities.

A short blurb on association classes can be found here.

ERD