Designing a database tables for the following scenario

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 1

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

Answer 2

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.