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