Sql-server – How to model the following problem

database-designsql server

Let's say, I have the following tables in MS-SQL Database:

Please note: This database composition is just fictional, because I'am not allowed to share sensitive business data.

Category

  • Id (int, pk)
  • Name (varchar(50))

Game

  • Id (int, pk)
  • Name (varchar(50))
  • CategoryId (int, fk: Category->Id)
  • etc…

Participant

  • Id (int, pk)
  • Name (varchar(100))
  • etc…

Composition

  • Id (int, pk)
  • ParticipantId (int, fk: Participant->Id)
  • GameId (int, fk: Game->Id)
  • Date (date)
  • Time (time)

The problem is that, only one Game per Category per Participant is allowed for a given Date and Time.

So for example:

Tim can participate only in one game from one category at a time.

Valid composition:

  • Tim -> Football (Ball games) -> 2017/02/02 -> 12:00
  • Tim -> DOOM (Video games) -> 2017/02/02 -> 12:00
  • Tim -> Football (Ball games) -> 2017/02/02 -> 13:00
  • Tim -> DOOM (Video games) -> 2017/02/02 -> 13:00

Invalid composition:

  • Tim -> Football (Ball games) -> 2017/02/02 -> 12:00
  • Tim -> Baseball (Ball games) -> 2017/02/02 -> 12:00

My first idea was to set a unique index to Composition table ParticipantId + GameId + Date + Time, but it still allows me to add two or more games from the same Category.

Finally the question is, is it a design problem, or I just have to add a check constraint to maintain data consistency?

Best Answer

One way to enforce this is to add CategoryID in Composition. Then you can add a UNIQUE constraint on (ParticipantID, CategoryID, Date, Time).

This requires to modify the foreign key to Game and to add a UNIQUE constraint on Game (or modify the existing one). The tables that need changes:

Game
    Id (int, pk)
    Name (varchar(50))
    CategoryId (int, fk: Category->Id)
    etc...
      UNIQUE (CategoryID, Id)                            -- added

Composition
    Id (int, pk)
    ParticipantId (int, fk: Participant->Id)
    CategoryId (int)                                     -- added
    GameId (int) 
      FK (CategoryId, GameId) -> Game (CategoryId, Id)   -- modified
    Date (date)
    Time (time)
      UNIQUE (ParticipantID, CategoryID, Date, Time)      -- added
Related Question