I want to store data of customers and employees. What I am having trouble deciding is what is the correct thing to do:
a. Option a: Two tables - Customer and Employee
b. Option b: Three tables - Person, Customer, and Employee
So for Option b, what I had in mind was that common properties of customers and employees like FirstName, LastName, etc. are on Person then related to Customer or Employee if he/she is a customer or employee (or both). Then Customer and Employee table would only have fields specific to them. I know I would need to join to query with Option b but I don't think that is such a big deal.
I'm not sure if Option b is appropriate or I'm mixing OOP concepts with designing so I wanted to ask in the perspective of someone who thinks only for the database in the context of designing, not specifically for performance.
Best Answer
I'd go b. A person is a person. Record that then layer over what role they take in your organisation.
Consider that an employee might also be a customer. A person might take the role of other participants that you have not yet thought of or chosen to model.
A customer can also be a business which would have a different shape.
Lots of potential reasons to keep them separate.