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

data modelingdatabasedatabase-designsqlsql server

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?

Best Answer

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)

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Sql-server – “with (nolock)” in SQL Server

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it IMHO.