使用一个拥有三个外键的联结表还是使用两个拥有共享排序的联结表？

Question

我有3个表格，我正在试图正确地在它们之间建立关系：

• 游戏阶段
• 游戏事件（'游戏阶段'中的一步）
• 动作窗口（'游戏阶段'中一种完全不同的步骤）

'游戏阶段'可以具有一个或多个'游戏事件'，而'游戏事件'可以发生在一个或多个'游戏阶段'中。 （多对多）

'游戏阶段'可以具有一个或多个'动作窗口'，而'动作窗口'可以在一个或多个'游戏阶段'中发生。 （多对多）

'游戏事件'与'动作窗口'无关。

然而，我犹豫是否要创建2个单独的连接表，因为'游戏事件'和'动作窗口'都共享相同的发生顺序（即序列）编号系统，每个'游戏阶段'。 我不确定如何维护它。

在仍能够轻松跟踪发生顺序的情况下，最佳的布局这些表格的方式是什么？

- Glyph

---

那么，“游戏事件”和“动作窗口”是按照发生顺序相关联的吗？我的直觉建议使用一个单一的交叉表，以序列号作为主键。你能发布一些（部分）表定义吗？ - Darwin von Corax

序列号必须是可编辑的。游戏规则会发生变化，因此我不想通过将其设置为主键而将其定死。它可以作为另一列工作。我仍然不确定是否要创建任何带有三个或更多外键的连接表。它必须具有一个约束条件，只允许填写“游戏事件”ID或“操作窗口”。 - Glyph

对于任何给定的“游戏阶段”，“事件”和“动作”的数量是否总是相同的？换句话说，某个阶段可能有5个事件和6个动作吗？如果是，它们的序列号将是什么？如果是，我会创建两个单独的交汇表，否则我会创建一个交汇表。 - Vladimir Baranov

游戏事件和动作窗口都是步骤，这两个表格是否非常不同还是相当相似？您能展示一下您的表格定义吗？ - Thorsten Kettner

@VladimirBaranov 是的，一个阶段可以有5个事件和6个动作。序列号可以是1到11，按需要任意排序。 - Glyph

@Glyph，所以你需要11行的某个地方来存储从1到11的序列号。毕竟，单独为序列号设置一个额外的表可能是有意义的。 - Vladimir Baranov

Answer 1

请将一个表连接起来，如下：

游戏阶段步骤

+----+-----------+-------+----------+---------+
| id | phase_id  | rank  |   type   | step_id |
+----+-----------+-------+----------+---------+
|  1 |         1 |    1  | event    |       2 |
|  2 |         1 |    2  | action   |       1 |
+----+-----------+-------+----------+---------+

• id - 游戏阶段步骤的主键
• phase_id - 游戏阶段的外键
• rank - 步骤在游戏阶段中的唯一顺序
• type - 游戏阶段可以是事件或操作
• step_id - 游戏事件的 event_id 或操作窗口的 action_id 的外部引用

不知道你的 game_event 和 action_window 是如何构造的。 但如果你需要在单个查询中获取所有数据，那么可能需要执行两个左连接，一个连接到 game_event，另一个连接到 action_window，并从适当的连接表中返回所需字段，类似于以下内容：

game_phase_steps g
LEFT JOIN game_event e
    ON game_phase_steps.type = "event" 
        AND game_phase_steps.step_id = e.id
LEFT JOIN action_window a
    ON game_phase_steps.type = "action" 
        AND game_phase_steps.step_id = a.id

如果您的表结构不同，则可能需要使用以下选择语句：

SELECT CASE WHEN g.type = "event" THEN e.some_field ELSE a.some_field END

- Somnath Muluk

---

感谢您认真思考并回答问题。在您的示例中，一个外键列（step_id）是否可以指向两个主键？我正在使用PostgreSQL。这可能是我可以通过谷歌搜索到的内容，但我想问一下。 - Glyph

你不需要通过结构来绑定外键，可以在逻辑上进行绑定。如果ID类型是“事件”，则step_id将成为game_event表的主键。如果您想要创建紧密的结构，则可以创建两个不同的带有外键引用的表。并且再创建一个引用这两个表以进行排序（即排名）的表。您可以参考此参考资料。 - Somnath Muluk

Answer 2

我建议设计一个代表游戏事件和动作窗口序列的设计，它们通过属于同一序列步骤来相互关联。

也就是说，引入“游戏序列”和“游戏序列详细信息”实体，通过在“游戏序列”中具有相同的序列步骤来表示“游戏事件”和“动作窗口”的共享排序事实（避免使用连接表的设计），因为考虑到“游戏事件与动作窗口无关”。

SCHEMA

+--------------+ 1      1,n +-------------------+ 1,n
|  game_phase  +------------+  game_phase_play  +----+
|              |            |                   |    |
+--------------+            +-------------------+    |
                                                     |
                                                     | 1
+--------------------------+ 1,n         1 +---------+---------+
|  gaming_sequence_detail  +---------------+  gaming_sequence  |
|                          |               |                   |
++--------+----------------+               +-----+-------------+
 | 1,n    |                                      |
 |        +---+  seq_step                        +---+ seq_steps_number
 |
 |
 |                                      +--------------+
 |                                +-----+  game_event  |
 | 1,n +----------------+         |     |              |
 +-----+  gaming_value  | <-------+     +--------------+
       |                |         |     +-----------------+
       +----------------+         +-----+  action_window  |
                                        |                 |
                                        +-----------------+

前缀

gph_                    === game_phase_
gseq_                   === gaming_sequence_
gv_                     === gaming_value_

表格

game_phase              ( id , description, ... )
game_phase_play         ( id , gph_id, gseq_id, date, description, ... )
gaming_sequence         ( id, seq_steps_number, ... )
gaming_value            ( id , type, ... )
gaming_sequence_detail  ( gseq_id, gv_id, seq_step, ... )

我还为“游戏事件”和“动作窗口”引入了一个通用实体，名为“游戏值”，适用于被包含在“游戏序列”中。这种概括可以表示一种游戏不可变值（在我们的情况下有2种不同类型，但可扩展），因此选择了这个命名（从“游戏物化”和“游戏可观察”之间切换）。

两种类型的“游戏值”在“游戏序列”中通过gaming_sequence_detail中(gseq_id, seq_step)对的相等性“步进在一起”。（有人可能会认为这种“配对”是一种关系；这种解决方案更像是在表示序列步骤中的兄弟姐妹，还允许扩展到超过2种类型的“游戏值”）。

“游戏序列详细信息”中的“游戏事件”和“动作窗口”可以独立修改，也可以出现“未配对”的情况。 gaming_sequence_detail中的约束是对于每个seq_step值，都可以存在一个game_event和一个action_window，因此单个gseq_id的seq_step的最大基数为2。

如果是这种情况，同一gaming_value也可以在同一序列中出现多次。此外，这种设计还可以表示不同“游戏阶段玩法”之间共享的序列。

- rfb

---

嗨，谢谢回复。你能解释一下 gaming_observable 是什么吗？ - Glyph

嗨，@Glyph。我在给这个实体命名时遇到了困难，可能是因为我的英语不太好。无论如何，我已经从“游戏物化”转换为更合适的名称“游戏价值”。它是一种游戏价值类型，被隔离在不同的不可变值中。 - rfb

我已经更新了新的命名和解释。感谢您的投票 :) - rfb