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!