php - 使用关系数据库 (mySQL) 的用户可扩展实体的方法

Question

我们有一个包含大约 5 个核心实体的 php/mysql 系统。我们现在需要为客户添加在每个项目的基础上为其中一些实体创建自定义字段的能力。

它们将包含标签、键、类型、默认值和可能的允许值。

这样他们就可以向 UI 添加自定义日期字段或自定义下拉列表，并针对特定实体保存此值。

在 mySQL 数据库中存储此类数据的最佳方法是什么？我需要存储字段的配置，然后存储特定实体的当前值。

我在这里查看了各种选项.. https://ayende.com/blog/3498/multi-tenancy-extensible-data-model

但这并不是真正的租户级别，更多的是项目级别。

我在想...

• 一个 CustomFields 表，用于保存针对实体类型和项目 ID 的字段配置。
• 一个 CustomFieldValues 表，用于保存针对字段保存的值 - 每个字段一行 (entity_id | field_id | field_value)

然后我们在检索实体时创建实体与这些自定义值之间的关系。

问题在于值表中的行数与自定义字段数一样多 - 因此保存实体将导致 X 额外行。最重要的是，这些是版本化的，因此一旦创建了新版本，就会为该新版本创建另外 X 行。

此外，您不能为名称上的字段建立索引，我认为连接会变得非常复杂，因为您必须连接到配置和值以构建键值对以针对实体返回，以及您将如何选择基于自定义字段名称，当字段名称实际上是一个值时？

我不想向表中添加动态列，因为这会影响整个系统中的所有实体 - 而不仅仅是当前客户/项目中的实体。

另一种选择是将值存储在 JSON 列中。

这可能在实体行本身 customFields 或类似的地方。这将防止每个字段的额外行，但也有缺乏索引等问题，并且仍然需要连接到配置表。但是，如果 key=value 存储在 JSON 中，您可以通过属性名称执行查询... WHERE entity.customFields->"$.myCustomFieldName"> 1 .

将文件名存储在 json 中确实意味着一旦创建就无法更改它，这会很痛苦。

如果有人对这方面的方法有任何建议，或者有文章指出我将不胜感激 - 我确信这已经解决了很多次......

Answer 1

JSON记录:没有!一千次不!如果您这样做，只需等到有人实际使用您的系统获取数千万条记录，然后要求您搜索您的额外字段之一。你的支持者会诅咒你的名字。

键值存储。可能是。有一个非常广泛部署的这种设计的存在证明:WordPress。它有一个名为 wp_postmeta 的表，包含适用于 wp_posts(博客页面和帖子)的元数据字段。它被证明是成功的。

您需要进行一些多重连接才能使用这些东西。例如，要搜索高度和眼睛颜色，您需要

 SELECT p.person_id, p.first, p.last, h.value height, e.value eye_color
   FROM person p
   LEFT JOIN attrib h ON p.person_id = h.person_id AND h.key='eye_color'
   LEFT JOIN attrib e ON p.person_id = e.person_id AND e.key='height'
  WHERE e.value='green' and CAST(h.value AS INT) <  160

正如 WHERE 子句中的 CAST 所示，您也会在数据类型方面遇到一些困难。

在这种属性查找中，您将需要 LEFT JOIN 操作；普通的内部 JOIN 操作会抑制缺少属性的行，这可能对您不起作用。

但是，如果您在索引方面做得很好，您将能够从这种方法中获得不错的性能。

我的示例中设想的表结构没有您的表来描述每个附加字段，但您知道如何添加它。它也没有明确支持多项目/ Multi-Tenancy 数据分离。但您也可以添加它。