Separate or Combine 2 Tables with Similar Attributes

database-designnormalization

I have two kind of users in my business system: Customer and Employee.
Both user have Username, Password, Fullname, Phone Number, Email, and other similar attributes.

I have a difficulty to determine which is better to merge
Customer and Employee on one table (for example I store in User table) or separate each entities on different table?

On my case, the Customer has additional attributes that the Employee doesn't had (for example: NewsUpdateSubscription). And also for Employee, it has additional attributes that Customer doesn't had (for example: Salary). What is the best practice for this case? Thanks in advance.

Best Answer

I'd say "Customer" and "Employee" are two roles a person can play in your system. Thus, I'd add concept of "Role" to your design. Then you can store customer related attributes in one table, and employee related attributes in another. You will have something like 

User (user_id, name, ...)
Role (role_id, description)
UserRole (user_role_id, user_id,role_id, date_from, date_thru) 
-- note, dates are quite important(at least date_from should be not null)
CustomerRoleAttribute(user_role_id, NewsUpdateSubscription)
EmployeeRoleAttribute(user_role_id, salary)

Also, to be absolutely correct, Customer and Employee roles require second part - for instance, I'm a customer of AAA company, and an employee of BBB, so my role is defined by relationship of certain type with other user (which is organization in case of "Employment" relationship).

You can find out more about this approach in "The Data Model Resource Book" by Len Silverston

Related Question