使用1个具有3个外键的联结表还是2个具有共享排序的联结表？ [英] Use 1 junction table with 3 foreign keys Or 2 junction tables with shared sequencing?

问题描述

我尝试正确地创建3个表，它们之间建立关系：

I have 3 tables that I am trying to properly create relationships between:

• 游戏阶段


• 游戏事件（游戏阶段中的一个步骤）


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

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

A 'game phase' can have one or more 'game event(s)', and a 'game event' can happen in one or more 'game phase(s)'. (Many-to-many)

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

A 'game phase' can have one or more 'action window(s)', and an 'action window' can happen in one or more 'game phase(s)'. (Many-to-many)

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

A 'game event' is not related to an 'action window'.

但是，我犹豫要创建2个单独的联结表，因为游戏事件和动作窗口共享相同的顺序每个游戏阶段的出现次数（aka序列）编号系统。我不确定如何保持这种状态。

However, I hesitate to create 2 separate junction tables because both 'game events' and 'action windows' share the same order of occurrence (aka sequence) numbering system per 'game phase'. I'm not sure how I would maintain that.

最合适的布置这些表的方式是什么，同时仍然能够轻松地跟踪发生的顺序？ / p>

What is the most proper way to lay out these tables while still being able to easily track the order of occurence?

推荐答案

我建议采用一种表示的 序列 的设计游戏事件 和动作窗口 通过属于同一序列步骤中的同一序列而彼此关联。

I suggest a design that represents sequences of 'game events' and 'action windows', related each other by belonging to a same sequence in the same sequence step.

也就是说，引入 游戏序列和 游戏序列详细信息实体，连接表避免通过设计表示 游戏事件和 动作窗口具有相同的游戏顺序中的顺序步骤 （避免使用 游戏事件与动作窗口 无关 ）。

That is to say, introducing "gaming sequence" and "gaming sequence detail" entities, the junction table is avoided "by design", representing the shared sequencing fact of 'game event(s)' and 'action window(s)' by mean of having the same sequence step in a "gaming sequence" (avoidance due to the consideration that "A 'game event' is not related to an 'action window'").

+--------------+ 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  |
                                        |                 |
                                        +-----------------+

< h2>参数

PREFIXES

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

表

TABLES

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种不同类型，但可扩展），因此选择的命名（已从 游戏目标化和 游戏可见切换） ）。

I've also introduced a generalization entity for 'game event(s)' and 'action window(s)' named "gaming value", useful for being contained in a "gaming sequence". This generalization could represent a kind of gaming immutable value (of 2 different types in our case, but extensible) and so the chosen naming (had been switching from "gaming objectified" and "gaming observable").

'游戏值'在中是 踩在一起博弈序列，通过等价于 gaming_sequence_detail 中的（gseq_id，seq_step）对的方式（可能会争辩这种 配对是一种关系；这种解决方案的作用更像是在顺序步骤中表示 sibiling ，还可以扩展到两种以上的' 游戏值'）。

'gaming value(s)' of the two types are "stepped together" in the gaming sequence by means of equality of the (gseq_id, seq_step) pair in gaming_sequence_detail (one could argue that this kind of "pairing" is a relationship; this kind of solution acts more like representing sibilings in a sequence step, permitting also to extend to more than 2 types of 'gaming values').

'游戏事件'和'动作窗口）中的游戏序列详细信息可以单独修改，也可以显示为 未配对。

'game event(s)' and 'action window(s)' in a gaming sequence detail could be independently modified and also could appear "unpaired".

gaming_sequence_detail 中的一个约束是，每个 seq_step 值可能存在一个 game_event 和一个 action_window ，因此单个的最大基数 seq_step gseq_id 为2。

A constraint in gaming_sequence_detail is that for every seq_step value could exist one game_event and one action_window and so the maximum cardinality of seq_step for a single gseq_id is 2.

如果是这种情况，相同的 gam ing_value 也可能以相同的顺序出现多次。此外，这种设计还可以表示不同游戏阶段玩法之间的共享序列。

If it's the case, the same gaming_value could also appear more than once in the same sequence. Moreover this design could also represent shared sequences between different 'game phase play(s)'.

这篇关于使用1个具有3个外键的联结表还是2个具有共享排序的联结表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！