Employee and Customer = People Table?

Question

Is it bad to store information such as: name, phone number, address etc. in one table? That would result in the Employee and Customer Table having a foreign key referencing to the "People" Table.

If we don't store such information in one table, we would have the Employee and Customer Table have a lot of similar type of information.

What would be the best design?

Answer 1

My personal preference: separate out customer and employee database. While some of the data may be same between customer and employee, it won't be long before data requirements and rules start differing. For example, for employees you may want to store birth dates but you may not need that for customer.

Keeping the table also prevents error in selects. If customers and employees were reference in people and a newcomer doesn't know what to join and when to use the where clause to separate customers and employees, the result may be unexpected and may go undetected. Having customer and employee separate avoids such issues.

Having them separate is also helpful in adding customer table to schema associated with customers/orders etc., and employee tables can go to hr related schema. Different levels of protection can be applied to them.

People table's maintenance may affect customer and employee tables. For example, if you had to add a column in a large people table, some RDBMS may lock the table too long. If tables were separate, you would be able to prepare only the relevant group.

Overall, I see little benefit in creating people table with FK related to customer and employees. Perhaps others on SO may raise benefits of people table.