时间戳

首页 > 解决方案 > 将表格的行关联到组中，每个组中都有特殊元素

mysql - 将表格的行关联到组中，每个组中都有特殊元素

问题描述

我有一张桌子items (id, varchar name)。我想创建一种将items 分组的方法。每个都item应该在一个中group。每个组应该至少有一个item，可能有多个。每个都group应该有一个item被指定为特殊的 s ，并且那item必须是 that 的成员group。应该可以选择 anyitem作为 selected item，并且应该可以更改 a 中的哪item一个group是选中的。

我正在使用最新版本的 MySQL，该items表使用 InnoDB。

如何将这些约束编码到 SQL 设计中？我知道如何创建一个 n 对 1 关联表，但我不知道如何编码“恰好一个特殊元素”部分。

我目前的尝试使用一张桌子group (id)和

group_item (
  id,
  group_id,
  item_id,
  unique key item_id,
  selected enum("selected") null,
  unique key (group_id, selected)
  )

但这并不能确保至少选择了一个元素，而且我不确定该设计是否朝着正确的方向发展。

不幸的是，我似乎找不到一个好的术语来搜索试图实现这种设计的文献或代码——我花了相当多的时间搜索谷歌和 Stack Overflow 以及一些 SQL 书籍.

标签： mysqldatabase-designforeign-keys

解决方案

这是一个可能的解决方案，但是，它不满足所有约束。

  1. 创建一个组表，让我们id为其标识符。

  2. 将组的外键添加到项目表中，group_id. 这满足了“每个项目都应该在一个组中”的约束。

  3. 组表应该有一个属性selected_item，项目表的外键。这满足了“每个组应该有一个被指定为特殊的项目”的约束。

此解决方案不满足的约束是：

  1. 每个组应该至少有一个项目。

  2. 每个组都应该有一个被指定为特殊的项目，并且该项目必须是该组的成员。

应用程序可以通过两个简单的查询来强制执行这两个约束：第一个约束可以通过检查或删除没有任何项目的组的查询来强制执行，例如：

DELETE FROM groups g 
  WHERE NOT EXISTS (SELECT * FROM items i WHERE i.group_id = g.id)

第二个带有查询，检查selected_item每个组中的 是否引用了具有该组的某些项目group_id。就像是：

SELECT *
FROM groups g
WHERE selected_item NOT IN (SELECT item_id FROM items i WHERE i.group_id = g.id)

AND selected_item IS NOT NULL（如果属性可以为空，则可能带有附加条件）。

或者，您可以添加触发器以自动执行约束。

推荐阅读