Separate or Combine 2 Tables with Similar Attributes?

Question

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.

Answer 1

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

Answer 2

While the names for these attributes may be the same, it's very likely that you'll want to store different values depending upon whether a person is a customer or an employee.

For example, if I work for a company but also use their website to purchase their products (so I'm an employee and a customer), when purchasing products I'll want to enter my personal phone number, email address etc. Whereas my employee record will need to store my work issued email address, phone and so on.

If you create a single users table to store all the common fields, if I want to change my contact number for purchases I've made, then my employee record will also be updated. If HR/IT use this information to know which phone I should return when leaving the company it could cause issues down the line if I'm free to change it in the customer context.

Therefore I'd recommend creating separate tables. The exception would be if you were in the unusual situation where your employees are your customers (which may be the case for an internal purchases application).