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
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.
You have a many-many (aka link or junction) table between the 2
- 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
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.