Is it good database design to have admin users in the same table as front-end users?
SqlSql ServerDatabaseDatabase DesignData ModelingSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
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 - Sql
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 - Sql
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.
Solution 6 - Sql
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.
Solution 7 - Sql
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?)
Solution 8 - Sql
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.
Solution 9 - Sql
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.