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