Is it good database design to have admin users in the same table as front-end users?

Question

I have users who can login on a front-end page, and admins who can login on an admin page.

Should both users and admins be "Users" with different roles, or should they be split in different tables?

Solution 1

Roles should be tracked separately from user accounts, because someone can be promoted (or demoted) over time. Would it make sense in that situation to have two different user accounts, in two different tables? I think not.

Here's the basic structure I'd use -

USERS

• user_id (primary key)
• user_name

ROLES

• role_id (primary key)
• role_name

USER_ROLES

• user_id (primary key, foreign key to USERS.user_id)
• role_id (primary key, foreign key to ROLES.role_id)

Solution 2

Yes, all users belong in the users table. You also need to have a Roles table and have a FK betweent the two.

Solution 3

The risk one a user accidentally becoming an administrative user shouldn't be bigger than a user accidentally becoming a different user, and that should definitely not happen either.

Consider that if you have regular users and administrative users in separate tables, you would have a user id in the regular user table matching a user id in the administrative user table. You would have to make sure that one type of user id could never be accidentally used as the other type. It's harder to spot a problem like that, than spotting something that could cause a user id changing into a different user id.

Solution 4

If admin and users share fields it seems they should go in the same table to avoid duplicating structure. They both have a first name and last name. Both are humans in the real world. This is probably the way it should be.

But on the other hand States and Cities both have a name. And both are locations. Should they always go in the same table? Sometimes they do in recursive models. Sometimes they are separate.

My thinking...... is admin considered to be a "type" of user in your system? Or is it something truly different where nothing of type "user" applies to it? It depends on what an admin really means in your system. Is the shared structure along the lines of city/state? Or is the shared structure along the lines of "you are TYPE user"?

But if in doubt go with putting admins in the user table because I doubt they are truly separate. You will probably want to share an authentication system for both. You will probably want to share account creation for both. Unless admin is some special thing only developers use on the back end.

Solution 5

I belive there is no absolute truth about your question, it depends on your application.

Two reasons the user-types could be in different tables would be:

• The types differ in data-structure (detail / address etc..)
• Good sleep. If you manually edit your FK-values (pointing at a user), you avoid the risk of pointing anything to a frontend-user.

View more solutions

Comments

[Teson]

The solution is excellent but users & users Can be different animals. Think of internet banks, they probably don't mix staff-login with ordinary customer-login in same table.

[TheBlackBenzKid]

For a simple to some what complex solution this works well. @user247245 is correct in terms of enterprise level - banks use PCI compliance etc. Some of our ecommerce stores are IBM WebSphere Commerce and we use a separate localhost IP and local machine to access our database in house. That is then sync real-time via a separate firewall server to the WWW into cloud load balancers.

[Z2VvZ3Vp]

Why not just put the role_id in the users table?

[Alix]

Think about god admin. If we want god admin to create roles and grant diff permissions (of diff modules) to them and assign the roles to other sub admins then it will have an entire different role system against users. Then it will be a bad idea to combine users and admins in such complex admin role sys of an integrated office automation module and for example an enterprise portal with CMS and CRM. Maintenance and code or db migrations(for update server) are other stuffs too. Also light admins table is faster than heavy users table in unique clustered indexed username column.

[Onur Yıldırım]

@PixMach (old post but) a user might have multiple roles assigned to it. i.e. a user can have both article_editor and account_manager roles. So USER_ROLES table manages one-to-many relations.

[rai]

If you are dealing with customer,for me I always prefer two tables. One for the customer, and one for the user(company staff). Its totally de-coupled from each other, easier, simplier. And because they are totally de coupled, its easy to implement authentication method without complicating the other table. Example if you want to authenticate customer via access_token(if they are separate app or SPA). By default, the admin users will leverage authentication using sessions or cookie.