在 SQLite（或 Postgres）中，您可以拥有一个行元素数量可变的表吗？

Question

我正在创建一个包含多种活动类型的活动表。让我们 假设“慢跑”类型的活动将包含元素 a、b 和 c，而 “足球”活动将包含要素 a、d 和 e。我可以创建一个 表中每列的行元素取决于该列的 类型？我考虑过为每种活动类型创建一个表或 创建一个表，其中包含每个活动选项的行，但是 会有很多活动类型，所以使用起来似乎很浪费，所以 许多表或将许多行留空。

Answer 1

您无法创建这样的表，数据库的本质不允许“可变参数”。这就是我们在数据库中建立关系来对此类内容进行建模的原因。

对于邪恶的快速黑客，您可以以特定格式将可变数量的参数存储在一列中，然后再次解析它。类似于“a:foo|e:bar|f:qux”。不要这样做，大约 1 天后就会失控。

我赞同詹姆斯的建议：重新设计你的桌子。它应该看起来像这样。

Table: Activities
id|activity
0|jogging
1|football
2|...

Table: ElementsOfActivities
id|activity_id|element
0|0|a
1|0|b
2|0|c
3|1|a
4|1|d
5|1|e

查找“规范化”（例如 http://en.wikipedia.org/wiki/Database_normalization ）

Answer 2

我假设在主题中您指的​​是列而不是行，因为表格的整个概念围绕着这样一个事实：有可变数量的行。您的陈述“将这么多行留空”也是如此 - 我再次假设您正在谈论列。

您所描述的本质上是一种称为“实体属性值”的（反）模式。搜索这个，你会发现很多点击描述如何做到这一点以及为什么不这样做。

在 Postgres 中，事情要容易一些。它有一个名为“hstore”的贡献模块，这基本上就是您正在寻找的模块。 “单列内有多个列”。

hstore 模块的最大缺点是失去类型安全性。您只能将字符数据放入 hstore 列中。所以你不能说“属性 *price”是数字，属性 name 是字符值”。

如果你可以忍受这个限制，hstore 可能就是你在 Postgres 中寻找的东西

Answer 3

情况很复杂。简短的回答是：“不”。
您应该问自己要报告什么，并尝试找出用于跟踪数据的不同模式。

如果你真的想实现一个可变列数表，你可以做一些接近的事情。

定义活动类型、要在每个活动类型上跟踪的元素以及用于解析多对多关系的联结表。这些表大部分是静态的。然后您有一个 Activity 表和一个 ActivityAttribute 表。

创建一个活动表，然后有一个活动类型、活动元素、活动类型-元素和活动属性表。

类型为“慢跑”、“足球”。

元素将是“a”，“b”，“c”，“d”...

类型元素将具有看起来像“慢跑：a”，“慢跑：b”，“慢跑：c”，“足球”的行:a", "football:d"

属性将具有实际数据： "18236:a:'0:10:24'", "18237:d:'356 码'"

Answer 4

表不是有限的资源（在合理的实践中），因此不要沉迷于创建大量“浪费”它们。类似地，在大多数现代数据库中，空列不占用空间（在 postgresql 中，超出最小的“空位掩码”开销），因此它们也不是特别宝贵的资源。

使用一个表来表示可以一起定义的不同属性集可能是有意义的（这本质上是数据库规范化的一般规则之一）。如果您想以通用方式处理“活动”，您可能希望在共享表中具有公共属性，就像 OOP 中的基类一样......或者您可能不需要。

例如，您可以：

jogging(activity_id int, a type, b type, c type)
football(activity_id int, a type, d type, e type)

然后创建一个视图，以便在需要时将它们组合在一起：

create view activity as
select 'jogging', activity_id, a, b, c, null as d, null as e from jogging
union all
select 'football', activity_id, a, null, null, d, e from football

或者您可以：

activity(activity_id int, a type)
jogging(activity_id int, b type, c type)
football(activity_id int, d type, e type)

然后：

create view activity as
select case when jogging.activity_id is not null then 'jogging'
            when football.activity_id is not null then 'football'
       end,
       activity_id, a, b, c, d, e
from activity
     left join jogging using (activity_id)
     left join football using (activity_id)

这些模型大部分是等效的，主要区别在于第二个提供了到不同的 Activity_id 标识符的清晰路径，这是许多人喜欢它的原因之一，特别是在使用 ORM 来持久化数据时（尽管您也可以通过共享序列来使用第一种方法）。