MySQL 数据库设计：3种类型的用户，单独的还是一张表？

Database design: 3 types of users, separate or one table?

提问by IMB

I have 3 types of users:

我有 3 类用户：

• Admins
• Suppliers
• Employees

• 管理员
• 供应商
• 雇员

Each user type will have different user interface and access different types of data. Their only similarity is they are using one web application but they access totally different things. Is it better to put them all in one user table like tbl_users or is it better to create tbl_admins, tbl_suppliers, tbl_employees?

每种用户类型将具有不同的用户界面并访问不同类型的数据。他们唯一的相似之处是他们使用的是一个 Web 应用程序，但他们访问的内容完全不同。将它们全部放在一个像 tbl_users 这样的用户表中更好还是创建 tbl_admins、tbl_suppliers、tbl_employees 更好？

回答by Michael Berkowski

What you need to consider when designing tables is not necessarily what they'll have access to and how that is similar/dissimilar, but rather how the user levels themselves are similar/dissimilar.

设计表格时您需要考虑的不一定是他们可以访问的内容以及它们的相似/不同之处，而是用户级别本身的相似/不同之处。

For example, if the user types will have the same attributes (name, email, birthdate, etc), then they belong in one table together with a column indicating their privilege level.

例如，如果用户类型将具有相同的属性（姓名、电子邮件、出生日期等），则它们与指示其权限级别的列一起属于一个表。

This also facilitates changing privilege levels for a user, whereby you can make an ordinary Employee into an Admin, for example, by just updating the record in the user table.

这也有助于更改用户的权限级别，例如，您可以通过更新用户表中的记录将普通员工变成管理员。

If Suppliers are a different type of object with different attributes than the other two, Suppliers may belong in their own table.

如果供应商是与其他两个具有不同属性的不同类型的对象，供应商可能属于他们自己的表。

Or, one more thing to consider: You might use a userstable that holds only very limited information about users of all three types, and if the types have extended attributes that don't relate well to one another, you can store those in other tables with a foreign key back to the main userstable.

或者，要考虑的另一件事是：您可能使用的users表仅包含有关所有三种类型的用户的非常有限的信息，并且如果这些类型具有彼此关联不佳的扩展属性，您可以将它们存储在其他表中用外键返回主users表。

回答by dasblinkenlight

There is also a third choice: put the columns that all users have in common into tbl_users, and create three tables for tbl_admins, tbl_suppliersand tbl_employeesjoining to tbl_usersas 1 to 0..1. You should consider this choice as an alternative when the number of shared columns is significant.

还有第三种选择：将所有用户共有的列放入tbl_users，并为 建立三个表tbl_admins，tbl_suppliers并tbl_employees加入tbl_users为 1 到 0..1。当共享列的数量很大时，您应该考虑将此选择作为替代方案。

回答by Aaron

It depends on how similar their data structures are. If they are similar, then perhaps you could put them all in one table. But, if they have a lot of different fields and you'll end-up with lots of NULL values...and then it's better that they're all in separate tables.

这取决于它们的数据结构有多相似。如果它们相似，那么也许您可以将它们全部放在一张表中。但是，如果它们有很多不同的字段，并且您最终会得到很多 NULL 值……那么最好将它们都放在单独的表中。

回答by pjbrown88

Best to keep all your login info in one place. If you were ever to make a change to your login process, having 3 different tables would mean having to change the code in 3 separate places.

最好将所有登录信息保存在一个地方。如果您要更改登录过程，拥有 3 个不同的表将意味着必须在 3 个不同的地方更改代码。

If a user can belong to more than one role, consider making a UserRoles table. Otherwise, adding an additional field to the existing table - such as RoleType - would help differentiate the different types of users.

如果用户可以属于多个角色，请考虑制作 UserRoles 表。否则，向现有表添加附加字段（例如 RoleType）将有助于区分不同类型的用户。

回答by divi

You should just include them in one table and create a field/attribute that would be an indicator of whether the user is an Admin, Supplier or Employee.

您应该只将它们包含在一个表中并创建一个字段/属性，作为用户是管理员、供应商还是员工的指示器。

It's simpler if you centralize it that way.

如果你以这种方式将它集中起来，那就更简单了。

The concern on how/what they access would be under the software you develop. You can either fetch/constrict the UI[or whatever they access in the software system] basing from the type of user you have.

在您开发的软件下，对他们访问的方式/内容的关注。您可以根据您拥有的用户类型获取/限制 UI [或他们在软件系统中访问的任何内容]。

I usually just hide and show stuff according to the type of user I have

我通常只是根据我拥有的用户类型隐藏和显示内容

Hope this helps..

希望这可以帮助..