用户设置的数据库设计 [英] 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_VALUE
s 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屋!