Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

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

share|improve this answer

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.

share|improve this answer
    
What normal form does LEAD violate? –  Walter Mitty Jul 12 at 18:17
    
@WalterMitty; not lead per se more the putative pure intersection table. It cannot have a candidate key since no combination of fields can be unique. No key, no normalisation. –  Michael Green Jul 13 at 3:38

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.