Adding an ID column to a join table (many-to-many) so that it can be targeted

database-design

I'm building a web application for a company that sells cars. They buy leads (personal details of people interested in buying cars) and try to turn them into customers. The company has a number of sales persons, and they want each lead to be distributed to 5 of their salespersons randomly.

However, every sales person must have his own individual copy of the lead. This is because sales persons work on a commission basis, so they need to be able to:

edit/add a lead's details without the other sales persons seeing it
manage tasks for each lead without the other sales persons seeing those tasks

The only option I see for this is to add an id (PRIMARY KEY) column to the salesperson_has_lead table, and change its salesperson_id and lead_id columns to foreign key columns.

However, I always read that it's wrong to add an ID (PRIMARY KEY) columns to a many-to-many join table. Does anyone see any flaws in the solution pictured above, or know of a better option? Any help will be very much appreciated!

Best Answer

Join tables normally have primary key which is composite : (saleperson_id, lead_id) in your case, each of these columns is also a foreign key to appropriate table. So there is no need in surrogate ID PK in simple cases.
However, you have tasks and person information (details) which are specific to relationship itself and should have FK to saleperson_has_lead (I'd get rid of "has" in table name, but it's outside of question scope). Thus, logically your model looks good to me. There are some options on how to implement this model :
1. Add a surrogate PK (as you did), but don't forget you still need unique constraint on (saleperson_id, lead_id)
2. Don't add it, but refer to already existing composite PK in saleperson_has_lead table -(saleperson_id, lead_id).

Personally I prefer the first approach because it seems more clear.

Side note. From what I see many of your entities (lead, saleperson, saleperson_has_lead) storing information about people. You may want to think of adding concept of role to your model because "lead" and "saleperson" are really just roles that a particular person can play in your system.

Related Solutions

How to design relationships for variant data

This might force a bit of change for your tables, but we do something like this at work:

Ensure each table has a int/guid primary key of some sort:

Organization Table

ID | Name
1  | MyOrganization

Then create a type-table to house your different LeadSource types:

LeadSourceTypeKey | Description
1                 | Organization
2                 | Company Office

Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType

Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:

SELECT *
FROM   [ProductSoldList] PL
       INNER JOIN [Organization] O
       ON PL.LeadSourceID = O.ID AND
          PL.LeadSourceType = 1
       INNER JOIN [Company Office] CO
       ON PL.LeadSourceID = CO.ID AND
          PL.LeadSourceType = 2

Normalization: Should common columns of two tables be pulled out into a third table

I see 3 options

Do nothing
Superkey/subtype, that is put the common fields into empl
Change to a self referencing table to build a hierarchy

Now,

Option 2 is similar to what you've described (mostly)
Option 3 is where you'd use a recursive CTE

However, I can't help thinking that Option 3 or some variety would be better. Perhaps some more info about what the columns mean? (Personally I don't abbreviate column names because the names are opaque)

Best Answer

Related Solutions

How to design relationships for variant data

Normalization: Should common columns of two tables be pulled out into a third table

Related Question