Is it allowed to for two tables to have two types of relationships between them? 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
---------------|------&lt;|----------
Title          |       |Body
---------------|-------|----------
CorrectAnswerId|       |QuestionId

So in my mind, a question can have many answers, and an answer can only be linked to one question -&gt; One-to-Many relationship between Question and Asnwer.
Also each question has one and only one correct answer -&gt; One-to-One relationship between Question and Asnwer.
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 &quot;allowed&quot;
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 &quot;might&quot; have)
This might sound a bit crazy, but as noted by ypercube in the answer linked in the comments, it is best to consider this a separate relation: for which we need a new table Question_CorrectAnswer, and it's PK is QuestionID.
This means that we only need to update a single row to change the correct answer.
Our only issue is that to prevent update anomalies, whereby someone could enter an Answer as being the correct one for a different Question, we must redefine Answer's primary key as being AnswerID, QuestionID, and FK on those two columns together.
So we define our tables like this:
(please don't take this as the best way to write CREATE TABLE, I've left out constraint names for example)
CREATE TABLE Question (
    QuestionId int IDENTITY PRIMARY KEY,
    Title varchar(100) NOT NULL
)

CREATE TABLE Answer (
    AnswerId int IDENTITY,
    QuestionId int NOT NULL REFERENCES Question (QuestionID),
    Body varchar(1000) NOT NULL,
    PRIMARY KEY (AnswerID, QuestionID)
)

CREATE TABLE Question_CorrectAnswer (
    QuestionId int NOT NULL PRIMARY KEY,
    AnswerId int NOT NULL,
    FOREIGN KEY (AnswerId, QuestionID) REFERENCES Answer (AnswerID, QuestionID)
)

It does have the downside of requiring an extra join, though.