Designing a database tables for the following scenario

Posted on

Question :

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.

Answer :

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

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.

Leave a Reply

Your email address will not be published.