How to normalize an association between weekly participations and weekly questions?

Question

I am implementing a contest system in which the user has to choose the correct answer of multiple questions. Each week, there is a new set of questions. I am trying to find the correct way to store the user participations in a database. Right now I have the following data model:

 Participation                     Week
+--------------+                  +--------------+
| Id           |     +----------->| Id           |<-+
| UserId       |     |            | StartDate    |  |
| WeekId       |-----+            +--------------+  |
+--------------+                                    |
                                   Question         |
                                  +--------------+  |
                                  | Id           |  |
                                  | WeekId       |--+
                                  | Text         |
                                  +--------------+

The only solution I came up with is to add an Answer table that associates a participation with a question, as indicated in the following diagram:

    Participation                     Week
   +--------------+                  +--------------+
+->| Id           |     +----------->| Id           |<-+
|  | UserId       |     |            | StartDate    |  |
|  | WeekId       |-----+            +--------------+  |
|  +--------------+                                    |
|                                     Question         |
|       Answer                       +--------------+  |
|      +------------------+    +---->| Id           |  |
+------| ParticipationId  |    |     | WeekId       |--+
       | QuestionId       |----+     | Text         |
       | Value            |          +--------------+
       +------------------+

I don't link this solution is very good, because it allows a participation to have answers to questions from a different week. Adding the WeekId to the answer does not help.

What is the correct way to represent this information?

Answer 1

Do you really need to associate a participation with its week? You can get it through Question

So:

• Answer(Id,UserId,QuestionId,Value)
• Question(Id,WeekId,Text)
• Week(Id, StartDate)

Answer 2

Personally I think you have the proper implementation here.

ParticipationId links to the ID on the participation, which is keyed with the user and the week. Your Question table is keyed with the WeekId as well.

Therefore, you have the proper references all along. If this is not the case I think we will need to see some data

Answer 3

You could remove the Id field in the table Participation, and use (UserId, WeekId) as composed/concatenated primary key for the table Participation. The field ParticipationId in the table Answer you have to replace then by the pair (UserId, WeekId) as foreign key reference to the table Participation. If your database system allows it, you can define then the fields (QuestionId, WeekId) in the table Answer to reference (Id, WeekId) in the table Question. Maybe for this you have to define an index on the pair (Id, WeekId) in the table Question before.