用户设置的数据库设计 [英] Database design for user settings

问题描述

如何设计数据库表以存储用户设置？

像这样？

OPTION1）

  USER_SETTINGS 
 -Id 
 -Code（exampleEmail_LimitMax）
 -Value示例5）
 -UserId 
  

需要您创建通知的设置

OPTION2）

 USER_ALERT_SETTINGS 
 -Id 
 -UserId 
 --EmailAdded（即true）
 --EmailRemoved 
 -PasswordChanged 
 ... 
 ... 
 
USER_EMAIL_SETTINGS
 -Id 
 -UserId 
 --EmailLimitMax 
 .... 
  

或

OPTION3）

 USER
 -Name 
 ... 
 -ConfigXML 
  

$ b b

解决方案

其他答案已经清楚地概述了各种选项的利弊。

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

请参阅以下ERD：

USER_SETTING 表是非常非常有用的类似于OP。区别是，这个设计不是varchar Code 和 Value 列，而是有一个FK到 SETTING 表定义允许的设置（代码）和值的两个互斥列。一个选项是varchar字段，可以接受任何类型的用户输入，另一个是FK到合法值表。

SETTING 表还具有一个标志，指示用户设置是否应由FK或非限制varchar输入定义。您还可以在 SETTING 中添加 data_type ，告诉系统如何编码和解释 USER_SETTING.unconstrained_value 。如果您愿意，您还可以添加 SETTING_GROUP 表，以帮助组织用户维护的各种设置。

此设计允许您围绕设置进行表格驱动规则。

---

编辑： 更多详细信息，包括一些示例...

请注意，上述ERD已扩充更多列详细信息

 <$ c $ 
 c>设置：
 + ---- + ------------------ + ------------- + --- ----------- + ----------- + ----------- + 
 | id |描述|约束| data_type | min_value | max_value | 
 + ---- + ------------------ + ------------- + ------- ------- + ----------- + ----------- + 
 | 10 |喜欢的颜色|真|字母数字| {null} | {null} | 
 | 11 |项目最大限制|假|整数| 0 | 9001 | 
 | 12 |项目最小限制|假|整数| 0 | 9000 | 
 + ---- + ------------------ + ------------- + ------- ------- + ----------- + ----------- + 
 
 ALLOWED_SETTING_VALUE：
 + --- -  + ------------ + -------------- + ----------- + 
 | id | setting_id | item_value |字幕| 
 + ----- + ------------ + -------------- + ----------- + 
 | 123 | 10 | ＃0000FF |蓝色| 
 | 124 | 10 | ＃FFFF00 |黄色| 
 | 125 | 10 | ＃FF00FF |粉色| 
 + ----- + ------------ + -------------- + ----------- + 
 
 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 | 
 + ------ + --------- + ------------ + --------------- ----------- + --------------------- + 
  

从这些表中，我们可以看到，可以确定的一些用户设置是喜好颜色，项目最大限制和项目最小限制。喜欢的颜色是字母数字的选择列表。项目最小和最大限制是设置了允许范围值的数字。 SETTING.constrained 列确定用户是否从相关的 ALLOWED_SETTING_VALUE 中选择，或者是否需要输入 USER_SETTING.unconstrained_value 。允许用户使用其设置的GUI需要了解提供哪个选项以及如何强制执行 SETTING.data_type 和 min_value



使用此设计，您可以将表格驱动器（如果有的话）设置为code>和 max_value 允许的设置包括足够的元数据，对用户选择（或输入）的值执行一些基本的约束/健全检查。

EDIT：示例查询

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

   -  DDL和样本数据填充... 
 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，'Favorite Color'，'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， 
（8901,234,12，NULL，'1'）
（7890，234，11，NULL，'100' 
  

现在，DML提取用户的设置：

   - 显示给定用户的设置
 select 
 US.user_id 
，S1.description 
，S1.data_type 
，当S1.constrained ='true'时的情况
然后AV.item_value 
 else US.unconstrained_value 
结束值
，AV.caption 
从USER_SETTING US 
 inner join SETING S1 
 on US.setting_id = S1.id 
 left outer join ALLOWED_SETTING_VALUE AV 
 on US.allowed_setting_value_id = AV.id 
其中US.user_id = 234 
  

在 SQL Fiddle 。

How would you design the database table to store user settings?

Like this?

OPTION1)

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

or create a new table for settings where required if settings for notification is required you create

OPTION2)

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

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

OR

OPTION3)

"USER"
-Name
...
-ConfigXML

解决方案

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.

See the following ERD:

In the above ERD, the USER_SETTING table is very similar to OP's. The difference is that instead of varchar Code and Value columns, this design has a FK to a SETTING table 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.

The SETTING table 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_type to the SETTING to tell the system how to encode and interpret the USER_SETTING.unconstrained_value. If you like, you can also add the SETTING_GROUP table to help organize the various settings for user-maintenance.

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

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.constrained column 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_type and the min_value and max_value limits, if they exist.

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:

-- 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:

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

这篇关于用户设置的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！