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.

1
  • "A single employee may be mapped to attend to many companies" but can a company have more than one employee mapped to it? Commented Sep 26, 2011 at 11:26

2 Answers 2

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

0
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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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