SQL 将管理员用户与前端用户放在同一张表中是否是好的数据库设计？

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

提问by Steven

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?

用户和管理员都应该是具有不同角色的“用户”，还是应该分成不同的表？

采纳答案by OMG Ponies

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

• user_id（主键）
• 用户名

ROLES

角色

• role_id (primary key)
• role_name

• role_id（主键）
• 角色名称

USER_ROLES

USER_ROLES

• user_id (primary key, foreign key to USERS.user_id)
• role_id (primary key, foreign key to ROLES.role_id)

• user_id（主键，USERS.user_id 的外键）
• role_id（主键，ROLES.role_id 的外键）

回答by Esteban Araya

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

是的，所有用户都属于用户表。您还需要有一个 Roles 表并且在两者之间有一个 FK。

回答by 800poundGorilla

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"?

我的想法...... admin 是否被认为是您系统中的“类型”用户？或者它是真正不同的东西，其中没有任何“用户”类型适用于它？这取决于管理员在您的系统中的真正含义。共享结构是沿着城市/州的路线吗？或者是“你是 TYPE 用户”的共享结构？

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.

但是，如果有疑问，可以将管理员放在用户表中，因为我怀疑他们真的是分开的。您可能希望为两者共享一个身份验证系统。您可能希望为两者共享帐户创建。除非 admin 是一些特殊的东西，只有开发人员在后端使用。

回答by Guffa

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 neverbe 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.

考虑到如果您在不同的表中拥有常规用户和管理用户，则常规用户表中的用户 ID 将与管理用户表中的用户 ID 匹配。您必须确保永远不会意外地将一种类型的用户 ID用作另一种类型。发现这样的问题比发现可能导致用户 ID 更改为不同用户 ID 的事情更难。

回答by StriplingWarrior

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.

我个人将“用户”放在一张表中。您决定如何表示角色（例如作为用户表本身的静态位，或通过高级 RBAC 权限）取决于您的系统的复杂程度。但用户就是用户。

回答by Teson

I belive there is no absolute truth about your question, it depends on your application.

我相信你的问题没有绝对的真理，这取决于你的申请。

Two reasons the user-types couldbe 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.

• 类型在数据结构（详细信息/地址等）上有所不同。
• 睡得好。如果您手动编辑 FK 值（指向用户），则可以避免将任何内容指向前端用户的风险。

回答by Amy

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

创建一个单独的 Roles 表和一个单独的 User_Roles 表。在第一个中定义角色，在第二个中将用户加入他们各自的角色（他们可能有多个角色？）

回答by Martijn

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.

从数据的角度来看，管理员是具有不同角色的用户是有道理的。每个用户权限可能有一个表，将用户与其角色相关联。用户可以有多个这样的角色，但归根结底，管理员就是用户。

回答by Mor Shemesh

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.

实际上最好将两者保存在同一张表上，因为它们拥有相同的数据类型。