sql - Postgres 中为一行存储一堆任意 bool 值的最佳方法是什么？

Question

我有一个装满食谱的数据库，每行一个食谱。我需要为每个食谱存储一堆任意的“标志”来标记各种属性，例如无麸质、无肉、无红肉、无 pig 肉、无动物、快速、简单、低脂肪、低糖、低热量、低钠和低碳水化合物。用户需要能够通过选中 UI 中的复选框来搜索包含一个或多个这些标志的食谱。

我正在寻找将这些属性存储在 Recipes 表中的最佳方式。到目前为止我的想法:

1. 为每个属性创建一个单独的列，并为每个列创建一个索引。我可能有超过 20 个这样的属性，所以我想知道在单个表上创建一大堆 BOOL 列是否有任何缺点。
2. 对所有属性使用位掩码并将整个内容存储在一个包含适当位数的数字列中。为每个位创建一个单独的索引，以便快速搜索。
3. 为每个标签创建一个带有值的 ENUM，然后创建一个包含该 ENUM 类型的 ARRAY 的列。我相信数组列上的 ANY 子句可以使用索引，但从未这样做过。
4. 创建一个单独的表，其中包含食谱到标签的一对多映射。每个标签将是该表中的一行。该表将包含指向配方的链接，以及该配方的标记为“打开”的 ENUM 值。查询时，我必须执行嵌套 SELECT 以过滤掉至少不包含其中一个标签的食谱。我认为这是更“正常”的做法，但它确实使某些查询更加复杂——如果我想查询 100 个食谱并显示它们的所有标签，我必须使用 INNER JOIN 并合并行，或使用嵌套的 SELECT 并动态聚合。

写性能在这里不是太大的问题，因为食谱是由后端进程添加的，搜索速度很关键(最终可能有几十万个食谱)。我怀疑我是否会经常添加新标签，但我希望它至少可以在没有大麻烦的情况下完成。

谢谢!

Answer 1

我建议您使用标准化设置。我不建议从一开始就将其设置为非规范化结构。

在不知道他正在做的事情的所有细节的情况下，我认为最好的设置是拥有您的食谱表和新的属性表以及一个新的 recipe_property 表。这允许配方具有 0 个或多个属性并规范化您的数据，从而可以快速轻松地维护和查询您的数据。

高级结构将是:

CREATE TABLE recipe(recipe_id);
CREATE TABLE property(property_id);
CREATE TABLE recipe_property(recipe_property_id,recipe_id,property_id);