MySQL 用户设置的数据库设计

Database design for user settings

提问by 001

Which of the following options, if any, is considered best practice when designing a table used to store user settings?

在设计用于存储用户设置的表时，以下哪个选项（如果有）被认为是最佳实践？

(OPTION 1)

（选项1）

USER_SETTINGS
-Id
-Code (example "Email_LimitMax")
-Value (example "5")
-UserId

(OPTION 2)

（选项 2）

create a new table for each setting where, for example, notification settings would require you to create:

为每个设置创建一个新表，例如，通知设置需要您创建：

"USER_ALERT_SETTINGS"
-Id
-UserId
-EmailAdded (i.e true)
-EmailRemoved 
-PasswordChanged
...
...

"USER_EMAIL_SETTINGS"
-Id
-UserId
-EmailLimitMax
....

(OPTION 3)

（选项 3）

"USER"
-Name
...
-ConfigXML

回答by Joel Brown

Other answers have ably outlined the pros and cons of your various options.

其他答案巧妙地概述了各种选择的优缺点。

I believe that your Option 1 (property bag) is the best overall design for most applications, especially if you build in some protections against the weaknesses of propety bags.

我相信您的选项 1（财产袋）是大多数应用的最佳整体设计，特别是如果您针对财产袋的弱点建立了一些保护措施。

See the following ERD:

请参阅以下 ERD：

In the above ERD, the USER_SETTINGtable is very similar to OP's. The difference is that instead of varchar Codeand Valuecolumns, this design has a FK to a SETTINGtable which defines the allowable settings (Codes) and two mutually exclusive columns for the value. One option is a varchar field that can take any kind of user input, the other is a FK to a table of legal values.

在上面的 ERD 中，该USER_SETTING表与 OP 非常相似。不同之处在于，该设计不是 varcharCode和Value列，而是对SETTING定义允许设置（代码）和值的两个互斥列的表进行了 FK 。一种选择是可以接受任何类型的用户输入的 varchar 字段，另一种是对合法值表的 FK。

The SETTINGtable also has a flag that indicates whether user settings should be defined by the FK or by unconstrained varchar input. You can also add a data_typeto the SETTINGto tell the system how to encode and interpret the USER_SETTING.unconstrained_value. If you like, you can also add the SETTING_GROUPtable to help organize the various settings for user-maintenance.

该SETTING表还有一个标志，指示用户设置应由 FK 还是由不受约束的 varchar 输入定义。您还可以添加data_type到SETTING告诉系统如何编码和解释USER_SETTING.unconstrained_value。如果您愿意，还可以添加SETTING_GROUP表格以帮助组织用户维护的各种设置。

This design allows you to table-drive the rules around what your settings are. This is convenient, flexible and easy to maintain, while avoiding a free-for-all.

这种设计允许您围绕您的设置来制定规则。这是方便、灵活且易于维护的，同时避免了所有人的自由。

---

---

EDIT:A few more details, including some examples...

编辑：更多细节，包括一些例子......

Note that the ERD, above, has been augmented with more column details (range values on SETTING and columns on ALLOWED_SETTING_VALUE).

请注意，上面的 ERD 已经增加了更多的列详细信息（SETTING 上的范围值和 ALLOWED_SETTING_VALUE 上的列）。

Here are some sample records for illustration.

以下是一些示例记录以供说明。

SETTING:
+----+------------------+-------------+--------------+-----------+-----------+
| id | description      | constrained | data_type    | min_value | max_value |
+----+------------------+-------------+--------------+-----------+-----------+
| 10 | Favourite Colour | true        | alphanumeric | {null}    | {null}    |
| 11 | Item Max Limit   | false       | integer      | 0         | 9001      |
| 12 | Item Min Limit   | false       | integer      | 0         | 9000      |
+----+------------------+-------------+--------------+-----------+-----------+

ALLOWED_SETTING_VALUE:
+-----+------------+--------------+-----------+
| id  | setting_id | item_value   | caption   |
+-----+------------+--------------+-----------+
| 123 | 10         | #0000FF      | Blue      |
| 124 | 10         | #FFFF00      | Yellow    |
| 125 | 10         | #FF00FF      | Pink      |
+-----+------------+--------------+-----------+

USER_SETTING:
+------+---------+------------+--------------------------+---------------------+
| id   | user_id | setting_id | allowed_setting_value_id | unconstrained_value |
+------+---------+------------+--------------------------+---------------------+
| 5678 | 234     | 10         | 124                      | {null}              |
| 7890 | 234     | 11         | {null}                   | 100                 |
| 8901 | 234     | 12         | {null}                   | 1                   |
+------+---------+------------+--------------------------+---------------------+

From these tables, we can see that some of the user settings which can be determined are Favourite Colour, Item Max Limit and Item Min Limit. Favourite Colour is a pick list of alphanumerics. Item min and max limits are numerics with allowable range values set. The SETTING.constrainedcolumn determines whether users are picking from the related ALLOWED_SETTING_VALUEs or whether they need to enter a USER_SETTING.unconstrained_value. The GUI that allows users to work with their settings needs to understand which option to offer and how to enforce both the SETTING.data_typeand the min_valueand max_valuelimits, if they exist.

从这些表中，我们可以看到可以确定的一些用户设置是最喜欢的颜色、项目最大限制和项目最小限制。最喜欢的颜色是字母数字的选择列表。项目最小和最大限制是设置了允许范围值的数字。该SETTING.constrained列确定用户是从相关ALLOWED_SETTING_VALUEs中挑选还是需要输入USER_SETTING.unconstrained_value. 允许用户使用其设置的 GUI 需要了解提供哪个选项以及如何强制执行 theSETTING.data_type和 themin_value和max_value限制（如果存在）。

Using this design, you can table drive the allowable settings including enough metadata to enforce some rudimentary constraints/sanity checks on the values selected (or entered) by users.

使用此设计，您可以表驱动允许的设置，包括足够的元数据，以对用户选择（或输入）的值实施一些基本的约束/健全性检查。

EDIT: Example Query

编辑：示例查询

Here is some sample SQL using the above data to list the setting values for a given user ID:

以下是一些使用上述数据列出给定用户 ID 的设置值的示例 SQL：

-- DDL and sample data population...
CREATE TABLE SETTING
    (`id` int, `description` varchar(16)
     , `constrained` varchar(5), `data_type` varchar(12)
     , `min_value` varchar(6) NULL , `max_value` varchar(6) NULL)
;

INSERT INTO SETTING
    (`id`, `description`, `constrained`, `data_type`, `min_value`, `max_value`)
VALUES
    (10, 'Favourite Colour', 'true', 'alphanumeric', NULL, NULL),
    (11, 'Item Max Limit', 'false', 'integer', '0', '9001'),
    (12, 'Item Min Limit', 'false', 'integer', '0', '9000')
;

CREATE TABLE ALLOWED_SETTING_VALUE
    (`id` int, `setting_id` int, `item_value` varchar(7)
     , `caption` varchar(6))
;

INSERT INTO ALLOWED_SETTING_VALUE
    (`id`, `setting_id`, `item_value`, `caption`)
VALUES
    (123, 10, '#0000FF', 'Blue'),
    (124, 10, '#FFFF00', 'Yellow'),
    (125, 10, '#FF00FF', 'Pink')
;

CREATE TABLE USER_SETTING
    (`id` int, `user_id` int, `setting_id` int
     , `allowed_setting_value_id` varchar(6) NULL
     , `unconstrained_value` varchar(6) NULL)
;

INSERT INTO USER_SETTING
    (`id`, `user_id`, `setting_id`, `allowed_setting_value_id`, `unconstrained_value`)
VALUES
    (5678, 234, 10, '124', NULL),
    (7890, 234, 11, NULL, '100'),
    (8901, 234, 12, NULL, '1')
;

And now the DML to extract a user's settings:

现在 DML 提取用户的设置：

-- Show settings for a given user
select
  US.user_id 
, S1.description 
, S1.data_type 
, case when S1.constrained = 'true'
  then AV.item_value
  else US.unconstrained_value
  end value
, AV.caption
from USER_SETTING US
  inner join SETTING S1
    on US.setting_id = S1.id 
  left outer join ALLOWED_SETTING_VALUE AV
    on US.allowed_setting_value_id = AV.id
where US.user_id = 234

See this in SQL Fiddle.

在SQL Fiddle 中看到这一点。

回答by Sajith

Consider this simple example.

考虑这个简单的例子。

If you have 2 tables, UserTable(contains user details) and SettingsTable(contains settings details). Then create a new table UserSettingsfor relating the UserTable and SettingsTable as shown below

如果您有 2 个表，UserTable（包含用户详细信息）和 SettingsTable（包含设置详细信息）。然后创建一个新表UserSettings用于关联 UserTable 和 SettingsTable，如下所示

Hope you will found the right solution from this example.

希望你能从这个例子中找到正确的解决方案。

回答by Tom Wilson

Option 1 (as noted, "property bag") is easy to implement - very little up-front analysis. But it has a bunch of downsides.

选项 1（如前所述，“财产包”）很容易实施 - 很少有预先分析。但它有很多缺点。

1. If you want to restrain the valid values for UserSettings.Code, you need an auxiliary table for the list of valid tags. So you have either (a) no validation on UserSettings.Code – your application code can dump any value in, missing the chance to catch bugs, or you have to add maintenance on the new list of valid tags.

2. UserSettings.Value probably has a string data type to accommodate all the different values that might go into it. So you have lost the true data type – integer, Boolean, float, etc., and the data type checking that would be done by the RDMBS on insert of an incorrect values. Again, you have bought yourself a potential QA problem. Even for string values, you have lost the ability to constrain the length of the column.

3. You cannot define a DEFAULT value on the column based on the Code. So if you wanted EmailLimitMax to default to 5, you can't do it.

4. Similarly, you can't put a CHECK constraint on the Values column to prevent invalid values.

5. The property bag approach loses validation of SQL code. In the named column approach, a query that says “select Blah from UserSettings where UserID = x” will get a SQL error if Blah does not exist. If the SELECT is in a stored procedure or view, you will get the error when you apply the proc/view – way before the time the code goes to production. In the property bag approach, you just get NULL. So you have lost another automatic QA feature provided by the database, and introduced a possible undetected bug.

6. As noted, a query to find a UserID where conditions apply on multiple tags becomes harder to write – it requires one join into the table for each condition being tested.

7. Unfortunately, the Property Bag is an invitation for application developers to just stick a new Code into the property bag without analysis of how it will be used in the rest of application. For a large application, this becomes a source of “hidden” properties because they are not formally modeled. It's like doing your object model with pure tag-value instead of named attributes: it provides an escape valve, but you're missing all the help the compiler would give you on strongly-typed, named attributes. Or like doing production XML with no schema validation.

8. The column-name approach is self-documenting. The list of columns in the table tells any developer what the possible user settings are.

1. 如果你想限制 UserSettings.Code 的有效值，你需要一个有效标签列表的辅助表。所以你要么 (a) 没有对 UserSettings.Code 进行验证——你的应用程序代码可以转储任何值，错过捕捉错误的机会，或者你必须在新的有效标签列表上添加维护。

2. UserSettings.Value 可能有一个字符串数据类型来容纳可能进入它的所有不同的值。因此，您丢失了真正的数据类型——整数、布尔值、浮点数等，以及 RDMBS 在插入不正确值时执行的数据类型检查。同样，您给自己买了一个潜在的 QA 问题。即使对于字符串值，您也失去了限制列长度的能力。

3. 您不能根据代码在列上定义 DEFAULT 值。因此，如果您希望 EmailLimitMax 默认为 5，则无法做到。

4. 同样，您不能在 Values 列上放置 CHECK 约束以防止无效值。

5. 属性包方法失去了对 SQL 代码的验证。在命名列方法中，如果 Blah 不存在，则查询“select Blah from UserSettings where UserID = x”将得到 SQL 错误。如果 SELECT 在存储过程或视图中，当您应用 proc/view 时，您将收到错误 - 在代码进入生产之前的方式。在属性包方法中，您只会得到 NULL。因此，您失去了数据库提供的另一个自动 QA 功能，并引入了一个可能未检测到的错误。

6. 如前所述，查找条件应用于多个标签的用户 ID 的查询变得更难编写——它需要为每个被测试的条件加入表。

7. 不幸的是，Property Bag 是对应用程序开发人员的一种邀请，只需将新代码粘贴到 property 包中，而无需分析它将如何在应用程序的其余部分中使用。对于大型应用程序，这成为“隐藏”属性的来源，因为它们没有正式建模。这就像用纯标签值而不是命名属性来做你的对象模型：它提供了一个逃生阀，但你错过了编译器在强类型命名属性上提供的所有帮助。或者就像在没有模式验证的情况下做生产 XML。

8. 列名方法是自记录的。表中的列列表告诉任何开发人员可能的用户设置是什么。

I have used property bags; but only as an escape valve and I have often regretted it. I have never said “gee, I wish I had made that explicit column be a property bag.”

我用过财产袋；但只是作为一个逃生阀，我经常后悔。我从来没有说过“哎呀，我希望我把那个明确的专栏变成了一个财产包。”

回答by Stephen Senkomago Musoke

Each option has its place, and the choice depends on your specific situation. I am comparing the pros and cons for each option below:

每个选项都有它的位置，选择取决于您的具体情况。我正在比较以下每个选项的优缺点：

Option 1: Pros:

选项 1：优点：

• Can handle many options
• New options can easily be added
• A generic interface can be developed to manage the options

• 可以处理多种选择
• 可以轻松添加新选项
• 可以开发通用接口来管理选项

Option 1: Cons

选项 1：缺点

• When a new option is added, its more complex to update all user accounts with the new option
• Option names can spiral out of control
• Validation of allowed option values is more complex, additional meta data is needed for that

• 添加新选项时，使用新选项更新所有用户帐户会更复杂
• 选项名称可能会失控
• 允许选项值的验证更复杂，为此需要额外的元数据

Option 2: Pros

选项 2：优点

• Validation of each option is easier than option 1 since each option is an individual column

• 每个选项的验证比选项 1 更容易，因为每个选项都是一个单独的列

Option 2: Cons

选项 2：缺点

• A database update is required for each new option
• With many options the database tables could become more difficult to use

• 每个新选项都需要更新数据库
• 有许多选项，数据库表可能变得更难使用

回答by Neville Kuyt

It's hard to evaluate "best" because it depends on the kind of queries you want to run.

很难评估“最佳”，因为这取决于您要运行的查询类型。

Option 1 (commonly known as "property bag", "name value pairs" or "entity-attribute-value" or EAV) makes it easy to store data whose schema you don't know in advance. However, it makes it hard - or sometimes impossible - to run common relational queries. For instance, imagine running the equivalent of

选项 1（通常称为“属性包”、“名称值对”或“实体-属性-值”或 EAV）可以轻松存储您事先不知道其架构的数据。但是，这使得运行常见的关系查询变得困难，有时甚至不可能。例如，想象一下运行相当于

select count(*) 
from USER_ALERT_SETTINGS 
where EmailAdded = 1 
and Email_LimitMax > 5

This would rapidly become very convoluted, especially because your database engine may not compare varchar fields in a numerically meaningful way (so "> 5" may not work the way you expect).

这将很快变得非常复杂，特别是因为您的数据库引擎可能无法以数字有意义的方式比较 varchar 字段（因此 "> 5" 可能无法按您期望的方式工作）。

I'd work out the queries you want to run, and see which design supports those queries best. If all you have to do is check limits for an individual user, the property bag is fine. If you have to report across all users, it's probably not.

我会计算出您想要运行的查询，并查看哪种设计最能支持这些查询。如果您所要做的只是检查单个用户的限制，那么财产包就可以了。如果您必须报告所有用户，则可能不是。

The same goes for JSON or XML - it's okay for storing individual records, but makes querying or reporting over all users harder. For instance, imagine searching for the configuration settings for email adress "[email protected]" - this would require searching through all XML documents to find the node "email address".

JSON 或 XML 也是如此——存储单个记录是可以的，但会使查询或报告所有用户变得更加困难。例如，假设搜索电子邮件地址“[email protected]”的配置设置 - 这将需要搜索所有 XML 文档以找到节点“电子邮件地址”。