2

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.

  • "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
3

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

  • 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
4

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.