将数据存储在链接表中

Question

Supoose 我有以下内容：

tbl_options
===========
id    name
1     experience
2     languages
3     hourly_rate


tbl_option_attributes
=====================
id    option_id    name          value
1     1            beginner      1
2     1            advanced      2
3     2            english       1
4     2            french        2
5     2            spanish       3
6     3            £10 p/h       10
7     3            £20 p/h       20


tbl_user_options
================
user_id    option_id    value
1          1            2
1          2            1
1          2            2
1          2            3
1          3            20

在上面的示例中 tbl_user_options 存储用户的选项数据。我们可以为某些选项存储多个条目。

现在我希望扩展这一点，即对于“语言”，我希望用户能够指定他们对某种语言的熟练程度（基础/中级/高级）。还有其他字段将具有扩展属性。

所以我的问题是，这些扩展属性可以存储在同一个表（tbl_user_options）中还是我需要创建更多表？显然，如果我输入“语言熟练程度”字段，它将不适用于其他字段。但这样我就只有一个用户选项表需要管理。你怎么认为？

编辑：这就是我的建议

tbl_user_options
================
user_id    option_id    value     lang_prof
1          1            2         null
1          2            1         2
1          2            2         3
1          2            3         3
1          3            20        null

Answer 1

我的直觉是将用户/语言/熟练程度关系分成自己的表。即使您将其与其他选项保存在同一个表中，您也需要编写特殊的代码来处理语言情况，因此您不妨使用新的表结构。

Answer 2

除非您的数据模型不断变化，否则我宁愿使用 tbl_languages 和 tabl_user_languages 表来存储这些类型的数据：

tbl_languages
================
lang_id    name
1          English
2          French
3          Spanish

tbl_user_languages
================
user_id    lang_id      proficiency  hourly_rate
1          1            1            20
1          2            2            10
2          2            1            15
2          2            3            20
3          3            2            10

设计一个“太通用”的系统是图灵关系 SQL 数据库的 tarpit 陷阱。基于文档的数据库更适合任意键值存储。

除了某些优化之外，您的数据库模型应尽可能匹配您的领域模型，以最大限度地减少对象关系阻抗不匹配。

通过此设计，您只需两个内部联接即可显示用户语言熟练程度和每小时费率的合理表：

SELECT
    ul.user_id,
    u.name,
    l.name,
    ul.proficiency,
    ul.hourly_rate
FROM tbl_user_languages ul
INNER JOIN tbl_languages l
    ON l.lang_id = ul.lang_id
INNER JOIN tbl_users u
    ON u.user_id = ul.user_id
ORDER BY
    l.name, u.hour

您可以选择将语言熟练程度列表拆分到 tbl_proficies 表中，其中 1 ==初级、2 == 高级、3 == 专家 并将其加入到tbl_user_languages 中。

Answer 3

我认为将“语言”作为选项是错误的。在阅读您的文本时，在我看来，英语是一个选项，并且它可能具有来自 option_attributes 的属性。