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 just got following entities Employee, Client and Company. Where Employee and Client have one to many relationship with Company ie A single employee may be mapped to attend to many companies and same for clients too. How would i design a optimized table for this situation. I had thought of below

Employee:

Id

Name

CompanyId

but since it is one to many companyid would have to be saved as comma seperated company id's. What do i do in this situation.

share|improve this question
    
"A single employee may be mapped to attend to many companies" but can a company have more than one employee mapped to it? –  Jack Douglas Sep 26 '11 at 11:26
add comment

2 Answers 2

up vote 3 down vote accepted

You have a many-many (aka link or junction) table between the 2

EmployeeCompany

Columns:

  • EmployeeID
  • CompanyID

Keys:

  • Primary key is (EmployeeID, CompanyID)
  • Add a unique index (CompanyID, EmployeeID)
  • EmployeeID has FK to Employeetable
  • CompanyID has FK to Company table

Do not store a CSV in a column: this is bad practice, can't enforce data integrity, can't search it efficiently, has no meaning etc

Note: A column called "ID" is very ambiguous so this is one case where you prefix with the table name

share|improve this answer
    
thank you for correcting me :). I got similar situations for some other tables too does this mean i keep on creating extra tables to solve such a many - to - many relation. –  Deeptechtons Sep 26 '11 at 11:27
    
@Deeptechtons: yes, this is correct design –  gbn Sep 26 '11 at 11:29
1  
thank you marked as answer and upvoted –  Deeptechtons Sep 26 '11 at 11:30
add comment

Assuming a company can be "attended to" by more than one employee, and likewise for client, you would model like this:

ER Diagram

So both relations are 'many:many'. As gbn also said, adding "comma separated ids" would be something of a disaster in database design: you would regret it many times over.

share|improve this answer
    
could you put the diagram on some other image hosting service, it's blocked by my corporate firewall :) thanks –  Deeptechtons Sep 26 '11 at 11:42
    
can you get it from here? –  Jack Douglas Sep 26 '11 at 12:00
    
wow i sure do. thanks for making it more clear with a diagram upvote –  Deeptechtons Sep 26 '11 at 12: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.