I have been going back and forth working with relational and no-sql databases for some time now and my questions may be the result of mixing the way of thinking about how to approach a problem.
So consider a simple multiple-choice quiz scenario where a question has an undefined number of answers (meaning it is not a fixed number of answers that each question has. One question could have two alternatives and another could have five). This question will have one correct answer. As such, if we want to have an initial translation between what I just wrote to SQL terms, it will be something like this:
Question Answer ===============| |========== Id | |Id ---------------|------<|---------- Title | |Body ---------------|-------|---------- CorrectAnswerId| |QuestionId
So in my mind, a question can have many answers, and an answer can only be linked to one question -> One-to-Many relationship between
Also each question has one and only one correct answer -> One-to-One relationship between
What I am trying to ask is, Is this design acceptable? If not what approach can I take?
One thing that I could do is to add a
IsCorrectAnswer flag to the
Answer entity. However it makes updating the correct answer to a question more difficult and (in my opinion) error-prone.
Clarification of what I mean by “allowed”
I would like the data to be normalized as much as possible (which I think won’t be with my first solution) and I would like not to have any update, insert and delete anomalies either (which I think the second solution “might” have)