3

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?

1

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
  • To be honest, couple hours ago I found a solution... but till now, I couldn't update my post. Anyway, my solution is almost identical to your answer, except that I modified the Game table PK from (Id) to (Id + CategoryId). Otherwise I don't know how could it be FK in other table. I don't think that a UNIQUE constraint is enough for it, but feel free to correct me, if I'm wrong. Thank you!! – s3rius Feb 2 '17 at 19:39
  • 1
    A PK means just unique constraint + not null. (you can have only 1 PK but as many as you like UNIQUE). It would work either way for the FK. – ypercubeᵀᴹ Feb 2 '17 at 19:41
  • 1
    Also note that the composite FK is needed for the desing to work right. – ypercubeᵀᴹ Feb 2 '17 at 19:42
0

Your table design is fine. I would add a trigger on table Composition. Whenever a row is inserted or updated then you have to check if the combination of Category, Participant, Date and Time is unique. A check constraint will not help in your case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.