其他答案已经详细阐述了您各种选项的利弊。
我认为对于大多数应用程序来说,您的选项1(属性包)是最佳设计,特别是如果您增加了一些保护措施来防范属性包的弱点。
请参阅以下ERD:
![Property Bag ERD](https://istack.dev59.com/My1Hp.webp)
在上面的ERD中,USER_SETTING
表与OP的非常相似。区别在于,这个设计没有varchar Code
和Value
列,而是有一个指向SETTING
表的FK,该表定义允许的设置(代码)和两个互斥列的值。其中一个选项是一个varchar字段,可以接受任何类型的用户输入,另一个选项是指向合法值表的FK。
SETTING
表还具有指示用户设置应由FK或无限制的varchar输入定义的标志。您还可以添加data_type
到SETTING
以告诉系统如何编码和解释USER_SETTING.unconstrained_value
。如果需要,您还可以添加SETTING_GROUP
表来帮助组织各种用户维护设置。
这种设计允许您驱动规则,以确定您的设置是什么。这很方便,灵活且易于维护,同时避免了自由发挥。
编辑:包括一些示例在内的更多详细信息...
请注意,上面的ERD已经增加了更多列细节(SETTING上的范围值和ALLOWED_SETTING_VALUE上的列)。
以下是一些示例记录,供说明使用。
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 |
+
从这些表中,我们可以看到一些可以确定的用户设置,如最喜欢的颜色、项目最大限制和项目最小限制。最喜欢的颜色是一个字母数字选择列表。项目最小和最大限制是具有可允许范围值设置的数值。"SETTING.constrained"列确定用户是从相关的 "ALLOWED_SETTING_VALUE" 中选择还是需要输入 "USER_SETTING.unconstrained_value"。允许用户使用其设置的GUI需要了解提供哪个选项以及如何强制执行 "SETTING.data_type" 以及如果它们存在,则为 "min_value" 和 "max_value" 设置限制。
使用此设计,您可以驱动数据表中的允许设置,包括足够的元数据来强制执行一些基本的约束/合理性检查所选值(或输入)的用户。
编辑:示例查询
这里是使用上述数据列出给定用户ID的设置值的一些示例SQL:
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')
;
现在是提取用户设置的DML:
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
在 SQL Fiddle 上查看此内容。