For our project we want to save employees and customer in one or two database tables.
The customers have the same columns as the employees (e.g. name, address, language, email,…). The employees on the other side do have additional columns like SocialSecurity Number, bankaccount,…
Since the two have many similar columns it might be senseful to merge them into a single ‘person’ table, considering that there might be from time to time a case where a customer get an employee or vice versa.
But since in the application this two ‘roles’ of people are strictly separated (we have querys where we want to get all customers and querys to get all employees, or search a person by email where role is customer / employee), then it might be more performant to keep them seperate.
Is there a big performance difference between this two solutions or is there even a third & better one?
Advertisement
Answer
I would not make this decision based on performance. I would make the decision based on security considerations and access rules.
In almost all circumstances I can think of, you would want separate tables for customers and employees. You could have a third table, persons, for common attributes such as names and addresses. That said, there are so many differences between the two, that I’m not even sure that is a good idea:
- Customers could be incorporated entities such as companies.
- Customers could be from anywhere in the world, but it is reasonable in many situations to assume that employees are local.
- Employees have dependents.
- You may maintain information such as gender, race, and age that you would not want to maintain about customers.
Those are just a few items that immediately come to mind. There are many other differences.