58

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Steven
  • 18,761
  • 70
  • 194
  • 296

9 Answers9

60

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)
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 13
    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. – Teson Nov 13 '10 at 14:36
  • 4
    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. – TheBlackBenzKid Aug 29 '12 at 12:05
  • 8
    Why not just put the role_id in the users table? – Z2VvZ3Vp Nov 30 '14 at 00:25
  • 2
    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. – AbbasAli Hashemian Dec 21 '15 at 16:50
  • 7
    @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. – Onur Yıldırım Dec 05 '17 at 22:46
  • 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. – rai Aug 17 '20 at 09:57
10

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

Esteban Araya
  • 29,284
  • 24
  • 107
  • 141
9

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.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
9

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.

800poundGorilla
  • 91
  • 1
6

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.
Teson
  • 6,644
  • 8
  • 46
  • 69
4

I'd personally keep "Users" in one table. How you decide to represent roles (e.g. as a static bit on the User table itself, or through advanced RBAC rights) depends on how complex your system is. But a user is a user.

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
3

Make a separate Roles table and a separate User_Roles table. In the first define the roles, in the second join users to their respective roles (it's possible they might have more than one?)

1

There should be no problem where you keep the users, only problem should be the pages\methods through which you access that information.

It would actually be better to keep both on the same table since they hold the same data type.

Mor Shemesh
  • 2,689
  • 1
  • 24
  • 36
1

From a data perspective it makes sense that administrators are users with different roles. There could be a table for each userright, correlating users with their roles. Users can have multiple roles like that, but at the end of the day, an administrator is a user.

Martijn
  • 11,964
  • 12
  • 50
  • 96