Use 1 junction table with 3 foreign keys Or 2 junction tables with shared sequencing?

Question

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

• game phase
• game event (a step in a 'game phase')
• action window (a radically different kind of step in a 'game phase')

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'.

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.

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

Answer 1

Make one table junction like:

game_phase_steps

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

• id - primary key for game_phase_steps
• phase_id - foreign key for game phase
• rank - unique order of occurrence of step in game phase
• type - game phase can have event or action
• step_id - event_id for foreign reference of game event or action_id for foreign reference of action window

Don't know how your game_event and action_window are structured. But if you need all data in single query, them you probably need to perform two left joins, one onto game_event and one onto action_window, and return the fields you want from the appropriate join table something like this:

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

and if your tables are structured differently then you might need select statement like this:

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

Answer 2

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'").

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

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, ... )

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 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".

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.

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)'.