创建另一个表只是为了存储一些选项？

I'm creating a database with various tables. Let's take the user table, for example. It has fields such as marital status and system role. Each of those fields has predefined options. Does it make sense to create two new tables for each of those fields, so then when a user is added to the system, choices can be made available for selection e.g. single, married, divorced? It seems a bit of an overkill in terms of one extra query. Is this the best way to do it or do I have other options?

我正在创建一个包含各种表的数据库。我们以用户表为例。它具有婚姻状况和系统角色等领域。每个字段都有预定义的选项。为每个字段创建两个新表是否有意义,因此当用户添加到系统时,可以选择选项,例如,单身,已婚,离婚?对于一个额外的查询而言,这似乎有点过分。这是最好的方法,还是我有其他选择?

5 个解决方案

#1

You can use the ENUM datatype in MySQL to better take care of this scenario. Storing such options in a seperate table is a bad idea until you have a lot of them..

您可以在MySQL中使用ENUM数据类型来更好地处理这种情况。将这些选项存储在单独的表中是一个坏主意,直到你有很多它们为止。

mysql> DESC Classes;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | NO   | PRI | NULL    |       |
| dept  | char(4)               | NO   |     | NULL    |       |
| level | enum('Upper','Lower') | NO   |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Classes;
+----+------+-------+
| id | dept | level |
+----+------+-------+
| 10 | MATH |       |
+----+------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO Classes VALUES (11, 'ENG', 'Upper')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM Classes;
+----+------+-------+
| id | dept | level |
+----+------+-------+
| 10 | MATH |       |
| 11 | ENG  | Upper |
+----+------+-------+
2 rows in set (0.00 sec)

#2

I would definitely create separate tables to store the available options for these various columns. This is a good thing to do as far as normalization goes, and will also save you headaches down the road when you need to add, remove, disable or change any of the options. Also, if don't create a separate table and populate the values directly in the user table, you may end up having to do something like select distinct RelationshipStatus from User to get the available options, which is not as performant as just selecting 10 or however many values from a separate table.

我肯定会创建单独的表来存储这些不同列的可用选项。就标准化而言,这是一件好事,并且当您需要添加,删除,禁用或更改任何选项时,这也将为您节省麻烦。此外,如果不创建单独的表并直接在用户表中填充值,您可能最终必须执行某些操作,例如从用户中选择不同的RelationshipStatus以获取可用选项,这不仅仅是选择10或但是来自单独表格的许多值。

As someone commented, over-normalization can sometimes be a pain, but I've found that not normalizing something as a way to do a quick work-around almost always comes back to haunt you.

正如有人评论的那样,过度规范化有时可能是一种痛苦,但我发现,没有将某些东西正常化作为一种快速解决方法几乎总是会让你感到困扰。

User
----
ID
RelationshipStatusId
...other columns


RelationshipStatus
------------------
ID
Value
Description

#3

For design's sake, create another table (what you don't want to do) with a proper PK. This will have the extra benefit of saving space, because imagine having 10000 registers with the word "married" on them.

为了设计的缘故,用适当的PK创建另一个表(你不想做的)。这将有节省空间的额外好处,因为想象一下有10000个寄存器,上面有“已婚”字样。

Also, an alternative is using in your application a "dictionary", storing in a structure and Id and the value, like this:

此外,另一种方法是在您的应用程序中使用“字典”,存储在结构和Id和值中,如下所示:

Id   Marital Status
 1   Married
 2   Single
 ..  ......

The same table, but not in a database but in the application, hardcoded, serialized or in an external file.

相同的表,但不在数据库中,而是在应用程序中,硬编码,序列化或在外部文件中。

#4

It depends on the size of the rows also. It would be better option to split the tables in to multiple in terms of speed. For ex. you can keep the frequent used columns in user table and all other informations/optional ones in separate tables. In this case you need take care while displaying the data also.

它还取决于行的大小。在速度方面将表格拆分为多个是更好的选择。对于前者您可以将用户表中常用的列和所有其他信息/可选列保存在单独的表中。在这种情况下,您还需要在显示数据时注意。

#5

I guess, there is no need for over-normalization as it will trouble you in writing queries. You need to take care of too many joins.

我想,不需要过度规范化,因为它会在编写查询时给您带来麻烦。你需要处理太多的连接。

If your predefined conditions for Marital Status are: Married, Single and Divorced, I would just store a single character like: M, S and D and would provide these options in a DropDown with fixed values.

如果您的婚姻状况的预定义条件是:已婚,单身和离婚,我只会存储一个字符,如:M,S和D,并在具有固定值的DropDown中提供这些选项。

I think Marital Status has no further possibilities unless you think of something like:

我认为婚姻状况除了你想到的东西之外没有其他可能性:

Want to be Divorced

想要离婚

Married but living alone.

结婚但独居。

For user role also, I would do something like that:

对于用户角色,我也会这样做:

A - Administrator P - Power User R - Restricted User G - Guest

A - 管理员P - 高级用户R - 受限用户G - 访客

In case you need something more elaborate, I won't create further tables.

如果你需要更复杂的东西,我不会创建更多的表。

#1