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

Question

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!

Answer 1

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.