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.

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.

http://i.stack.imgur.com/12k35.png

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.

http://i.stack.imgur.com/UPb27.png

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!

share|improve this question
    
Are multiple salespeople given the same lead? If so, do you actually care whether the have the same person, given that each salesperson can overwrite a lead's details with whatever they want? –  Chris Saxon Mar 3 '13 at 18:56
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you for the clear answer! –  Freek Vanraes Mar 3 '13 at 22:24
add comment

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.