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?

Answer 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)

Answer 2

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