database-designsql server
I am designing a database for a custom software application. The part I am focused on now is the ability to add custom questions to ask users.
I want to ensure that each user can only answer a question once. In this case, a user is a 'Provider'. How do I ensure each user only answers a question once?
There are 2 scenarios to consider. The first is when you have the user take 1 or more questions without a meaningful context such an exam or formal quiz. In which case you solution works. However, the second scenario is about when the user takes a question as part of a formal session. In this case you have the following rules:
Note:
Table names may be different in the solution than from this diagram
A1 = Standard answer for question 1
UA1 = User provided answer for question 1
Include the providerid column (and appropriate values) in the UserRating table.
Make (userid, providerid) a unique key for the UserRating table.
Make (id, providerid) a unique key of CreditRating.
Make (ratingid, providerid) a foreign key of UserRating, referencing (id, providerid) in the CreditRating table.
Also, make (providername, typename) a unique key of Provider, if you haven't already. Or better yet, normalize them into separate provider and type tables, with a many-to-many join as appropriate.
Best Answer
A unique constraint on Answers (questionid, providerid) was needed, as suggested by Balazs Papp.
As explicitly pointed out by Andriy M this should be a composite key.
I had to look up composite keys, as I was unfamiliar. Thanks so much to everyone for helping and responding so quickly.