Enforce partitions in sql

user_369009 0
Viewed 29

Game night! I have some players:

Player (primary key) Name
0 Mary Poppins
1 Peter Pan
... ...

Playing various rounds of board games:

Round (primary key) Time
0 18:30
1 18:55
... ...

In each round, there are different groups of players coming together to play board games. Each specific game has a game summary of what happened:

Game Description (primary key) Summary
0 Mary started out strong, but Peter took the lead after ...
... ...

How can I combine these by adding/changing SQL tables with the following constraints enforced by the database:

  • Each Player can join at most one Game Description per Round
  • Each Game Description belongs to exactly one round

For example this solution:

Game Description (primary key, foreign key) Round (foreign key)
0 1
1 4
... ...
Player (foreign key) Game Description (foreign key)
0 1
1 1
2 3
... ...

Would allow storing the relations, but wouldn't prevent one Player to be part of multiple Game Descriptions in the same round...

sql
0 Answers
Related