Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
Thanks a1ex07, do we need primary key again on CustomerRoleAttribute & EmployeeRoleAttribute since user_role_id is foreign key from UserRole? I mean, do we need something like CustomerID & EmployeeID since each table need primary key? –  Willy Lazuardi Dec 30 '12 at 17:37
    
user_role_id can be primary key in those tables. –  a1ex07 Dec 30 '12 at 17:39

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).

share|improve this answer
    
Thanks a lot Chris :), what is your opinion about the same field (username and password for login to the system)? Is it a normal form to create 2 tables with nearly similar attributes? I mean why not put it on User table then map it to Customer and Employee table. Thanks in advance –  Willy Lazuardi Dec 30 '12 at 16:47
    
Your business rules for passwords are likely to be very different for customers and employees. Many companies have a policy requiring employees to change their password every 3-6 months; very few web applications I've used do the same. Managing the password rules around someone who is both an employee and a customer will get complex and confusing if managed using the same record. If entities have similar attributes but are subject to completely different rules and constraints, then it's perfectly valid to have two tables with similar fields. –  Chris Saxon Dec 31 '12 at 9:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.