MySQL问题 - 如何处理多种类型的用户 - 一个表或多个？ [英] MySQL Question - How to handle multiple types of users - one table or multiple?

问题描述

我为一个拥有多个类型用户的组织设计了一个数据库。首先，我只创建了一个用户表。但是，虽然所有用户都共享一些公共信息（名字，姓氏，用户名，密码等），但每个用户类型都需要一个或两个不适用于所有用户的附加字段。虽然我可以创建这些额外的字段并将其设置为NULL，我不想这样做，因为字段是外键，它一直对我造成问题。

I'm designing a database for an organization that has multiple "types" of users. At first, I created only one user table. However, while all users share some common information (first name, last name, username, password, etc.), each user type requires one or two additional fields that are not applicable to all users. While I can create these additional fields and set them as NULL, I do not wish to do this, as the fields are foreign keys and its been causing problems for me.

这种情况如何正常处理？

How is this situation normally handled?

谢谢！

推荐答案

本能不创造一个大表有很多NULLS是正确的。这是一个坏主意，从存储/检索/维护的角度，以及数据验证的观点（更多的是后面的）。

Your instincts to not create a big table with lots of NULLS is right on. That's a bad idea, from a storage/retrival/maintenance point of view, as well as a data validation point of view (more on that later).

通用Approcaches：

The two most common approcaches:

1）拥有一个包含所有公共字段的用户表，包括一个userType字段。然后为包含额外字段的每个用户类型分别创建一个表。所有用户在users表中有一行以及一个或多个特定的用户类型表。这是最规范化和最高效的存储和快速登录。这也允许你使用约束和外键，以确保每个用户类型的所有必需的信息可用。

1) Have a user table with all the common fields in it, including a "userType" field. Then have a separate table for each user type containing the extra fields. All users have a row in the users table and one or more of the specific user type tables. This is the most normalized and the most efficient for storage and quick logins. This also lets you use contraints and foreign keys to assure that all required information for each user type is available.

2）有一个用户表中所有的公共字段。拥有另一个名为 UserAttributes 的表，其中包含用户标识，键和值的字段。任何特定用户的额外元数据都可以存储在此处。这具有不需要任何数据库管理来添加要为每个用户类型存储的新的用户类型或元数据的优点。但是，它不允许您在数据库级别进行任何数据验证。

2) Have a user table with all the common fields in it. Have another table called something like UserAttributes that has fields for userid, key, and value. Any extra metadata for a particular user can be stored in here. This has the advantage of not requiring any database administration to add new user types or metadata to be stored for each user type. However, it doesn't let you do any data validation at the DB level.

这篇关于MySQL问题 - 如何处理多种类型的用户 - 一个表或多个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！