关系设计：列属性

Question

我有一个系统，允许人们从下拉框中选择他们想要填写的表单类型。由此，将显示该特定表单的其余字段，用户填写它们并提交条目。

表单表：

| form_id | age_enabled | profession_enabled | salary_enabled | name_enabled |

它描述了表单的元数据，以便系统知道如何绘制它。因此，如果表单应包含要为此列填写的字段，则每个 _enabled 列都是布尔值 true。

条目表：

| entry_id | form_id | age | profession | salary | name | country |

存储提交的表单。其中年龄、职业等存储表单中填写的实际值（如果表单中不存在则为空）

用户可以动态添加新表单到系统。

现在的主要问题是：我想为用户添加设计新表单的功能，使其能够包含属性的可能值列表（例如，职业是包含 20 个职业的下拉列表，而不仅仅是一个文本框）填写表格时）。我不能简单地存储每列的可能值的全局列表，因为每个表单都有不同的值列表可供选择。

我能想到的唯一解决方案是在表单表中包含另一组列，例如 professional_values，然后以字符分隔格式存储值。我担心有一天某一列可能会有大量可能的值，并且该列将失去控制。

请注意，如有必要，可以稍后将新列添加到 Form（依次添加 Entry），但 90% 的表单具有相同的基本列集，因此我认为这种设计比 EAV 设计更好。想法？

我从未见过这样一个系统（作为一个整体）的关系设计，而且我似乎无法找到一个合适的方法来做到这一点。

Answer 1

创建一个新表来包含值组：

CREATE TABLE values (
    id SERIAL,
    group INT NOT NULL,
    value TEXT NOT NULL,
    label TEXT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (group, value)
);

例如：

INSERT INTO values (group, value, label) VALUES (1, 'NY', 'New York');
INSERT INTO values (group, value, label) VALUES (1, 'CA', 'California');
INSERT INTO values (group, value, label) VALUES (1, 'FL', 'Florida');

因此，组 1 包含下拉选择器的三个可能值。然后，您的表单可以引用特定列使用的组。

另请注意，您应该通过行而不是列将字段添加到表单中。即，当您添加新表单时，您的应用程序不应该调整架构，它应该只创建新行。因此，将每个字段设为自己的行：

CREATE TABLE form (
    id SERIAL,
    name TEXT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE form_fields (
    id SERIAL,
    form_id INT NOT NULL REFERENCES form(id),
    field_label TEXT NOT NULL,
    field_type INT NOT NULL,
    field_select INT REFERENCES values(id),
    PRIMARY KEY (id)
);

INSERT INTO form (name) VALUES ('new form');
$id = last_insert_id()
INSERT INTO form_fields (form_id, field_label, field_type) VALUES ($id, 'age', 'text');
INSERT INTO form_fields (form_id, field_label, field_type) VALUES ($id, 'profession', 'text');
INSERT INTO form_fields (form_id, field_label, field_type) VALUES ($id, 'salary', 'text');
INSERT INTO form_fields (form_id, field_label, field_type, field_select) VALUES ($id, 'state', 'select', 1);

Answer 2

我认为你完全从错误的地方开始。

| form_id | age_enabled | profession_enabled | salary_enabled | name_enabled |

您是否要继续向该表中添加您可以拥有的每个字段？一般来说，这个列表可能是无穷无尽的。

如果所有字段都在此表的列中，您的应用程序代码将如何显示表单？

像这样的表单怎么样：

| form_id | form description |

然后是另一个表，formAttributes，表单上每个条目一行：

| attribute_id | form_id | position | name | type | 

然后是第三个表 forAttributeValidValues，每个属性有效值一行：

| attribute_id | value_id | value |

这可能看起来需要更多工作，但实际上并非如此t。想一想向表单添加或删除新属性或值是多么容易。还要考虑您的应用程序将如何呈现表单：

for form_element in (select name, attribute_id 
                     from formAttributes 
                     where form_id = :bind
                     order by position asc) loop
  render_form_element
  if form_element.type = 'list of values' then
     render_values with 'select ... from formAttributeValidValues'
  end if
end loop;

接下来的困境将变成如何存储表单结果。理想情况下，您可以将它们存储在一个表中，每个表单元素 1 行，如下所示：

| completed_form_id | form_id | attribute_id | value |

如果您一次只处理一个表单，那么此模型将运行良好。如果您想对大量表单进行聚合，那么生成的查询会变得更加困难，但是这是报告，它可以在与在线表单输入不同的进程中运行。您可以开始考虑通过旋转查询将行转换为列或物化视图以将相同类型的表单组合在一起等的事情。