Database design: 3 types of users, separate or one table?

Question

I have 3 types of users:

• Admins
• Suppliers
• Employees

Each user type will have different user interface and access different types of data. Their only similarity is they are using one web application but they access totally different things. Is it better to put them all in one user table like tbl_users or is it better to create tbl_admins, tbl_suppliers, tbl_employees?

Solution 1

What you need to consider when designing tables is not necessarily what they'll have access to and how that is similar/dissimilar, but rather how the user levels themselves are similar/dissimilar.

For example, if the user types will have the same attributes (name, email, birthdate, etc), then they belong in one table together with a column indicating their privilege level.

This also facilitates changing privilege levels for a user, whereby you can make an ordinary Employee into an Admin, for example, by just updating the record in the user table.

If Suppliers are a different type of object with different attributes than the other two, Suppliers may belong in their own table.

Or, one more thing to consider: You might use a users table that holds only very limited information about users of all three types, and if the types have extended attributes that don't relate well to one another, you can store those in other tables with a foreign key back to the main users table.

Solution 2

There is also a third choice: put the columns that all users have in common into tbl_users, and create three tables for tbl_admins, tbl_suppliers and tbl_employees joining to tbl_users as 1 to 0..1. You should consider this choice as an alternative when the number of shared columns is significant.

Solution 3

It depends on how similar their data structures are. If they are similar, then perhaps you could put them all in one table. But, if they have a lot of different fields and you'll end-up with lots of NULL values...and then it's better that they're all in separate tables.

Solution 4

Best to keep all your login info in one place. If you were ever to make a change to your login process, having 3 different tables would mean having to change the code in 3 separate places.

If a user can belong to more than one role, consider making a UserRoles table. Otherwise, adding an additional field to the existing table - such as RoleType - would help differentiate the different types of users.

View more solutions