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

sql sql-server database database-design data-modeling
Steven picture Steven · Nov 13, 2010 · Viewed 25.5k times · Source

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?

Answer

OMG Ponies picture OMG Ponies · Nov 13, 2010

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 questions

What does ON [PRIMARY] mean?

I'm creating an SQL setup script and I'm using someone else's script as an example. Here's an example of the script: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[be_Categories]( [CategoryID] [uniqueidentifier] ROWGUIDCOL NOT …

Read More
Relationship of Primary Key and Clustered Index

Can a TABLE have a primary key without a clustered index? And can a TABLE have a clustered index without having a primary key? Can anybody briefly tell me the relationship between primary key and clustered index?

Read More
Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More