Entity Relationship Model - double one to many

Question

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'.

Answer 1

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).

Answer 2

If you construct a "pure" intersection table with just MId and CId columns, what would its primary key be? Even the composite of those two would not be sufficient because, by your stated rule, a customer can submit mulitple leads of different types at different times to the same manufacturer. So the natural key to the interaction between Customer and Manufacturer is MId, CId, Type and Date(time), which is what you've documented for Lead. Since it cannot have a unique primary key the "pure" intersection table cannot be a normalised relation. The stated design is perfectly fine and accords with good practice.

Lead.LId is a surrogate key in this context. If no other table has a foreign key pointing back to Lead I would be inclined to dispense with LId, but the difference is marginal.

Answer 3

In the Entity-Relationship data model, LEAD could be viewed as a many-to-many relationship between MANUFACTURER and CUSTOMER, a pair of entities. Relationships can have attributes of their own, as LEAD explicitly does. ER diagrams are sometimes used to depict ER models, but sometimes they are used to depict relational or SQL models.

In a relational model, MANUFACTURER, CUSTOMER, and LEAD are all relations.

In an SQL model, all three are tables. That seems to be the way you are using ER diagrams. In both the relational model and the SQL model, an intersection or junction table is used to implement a many-to-many relationship.

Nothing wrong with your design. It is probably satisfactory and may well be optimal. That depends on what you are doing.

update: if CID and LID do not uniquely identify a LEAD, then the above answer needs to be completely revised. In that case, LEAD is indeed a third entity, not a relationship.