1

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 Question and Asnwer.

Also each question has one and only one correct answer -> 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 "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)

0
1

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.

0

If you add CorrectAnswerId to your question table, you don't have to update all the answer rows, assuming only 1 question is correct. That being said, you probably may want to have multiple choice questions, right? Is not a big deal to have to update multiple rows in a transaction.

Your design will normalized enough with either option.

0
-2

As you seem to prefer a 1:1 relationship between questions and answers and you also don't want to reuse answers, add the CorrectAnswer to the answer, so you can have multiple correct answers per question and a re so more flexible.

CorrectAnswer is more a Attribute for answers than for questions, so it makes more sense to put it there

Question                Answer
===============|       |==========
Id             |       |Id
---------------|------<|----------
Title          |       |Body
                       |----------
                       |QuestionId
                       |----------
                       |CorrectAnswer

As i getting downvoted i expand my answer, as i claimed the correct answer belongs to the table answers.

You always have to put a bit of effort into it, not only on the part of the gui.

The stored procedure is only an example, you can get also the correct answers, if you collect the is that are tested. And with a bot of flow control, you can do anything in sql

CREATE TABLE Question (id int,Title  Varchar(50))
INSERT INTO Question VALUES (1,'Title1'),(2,'Title2')
CREATE TABLE Answer (Id int,body Text,QuestionId int,CorrectAnswer Tinyint )
INSERT INTO Answer VALUES (1,'text1',1,0),(2,'text2',1,1),(3,'text3',1,1),(4,'text4',2,0),(5,'text5',2,1),(6,'text6',2,0)
SELECT * FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1
Id | body  | QuestionId | CorrectAnswer
-: | :---- | ---------: | ------------:
 2 | text2 |          1 |             1
 3 | text3 |          1 |             1
SELECT DISTINCT 1  AS Result FROM Answer WHERE QuestionId = 1 AND Id =  1 AND CorrectAnswer = 1
| Result |
| -----: |
SELECT DISTINCT 1 As REeslit  FROM Answer WHERE QuestionId = 1 
AND  (3 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1)
AND  2 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1))
| REeslit |
| ------: |
|       1 |
SELECT DISTINCT 1 As Result  FROM Answer WHERE QuestionId = 1 
AND  (3 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1)
OR  2 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1))
| Result |
| -----: |
|      1 |
CREATE PROCEDURE CheckValid_Answer (IN _QuestionId INT,
  IN _ChoosenNumberList varchar(4000), OUT NoCorrectAnswer INT, OUT NoFalseAnswer INT)
BEGIN
  DECLARE finished INTEGER DEFAULT 0;
        DECLARE _int INT;
  -- declare cursor for answer numbers
  DEClARE curAnswers 
      CURSOR FOR 
          SELECT DISTINCT val FROM temp;
          


  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
        -- Get all answer numvers
        SET @ChoosenNumberList := _ChoosenNumberList;
        SET @QuestionId := _QuestionId;
        CREATE TEMPORARY TABLE temp (val INT);
        SET @S1 = CONCAT("INSERT INTO temp (val) VALUES ('",REPLACE((SELECT GROUP_CONCAT( DISTINCT  @ChoosenNumberList) AS data ), ",", "'),('"),"');");
        PREPARE stmt1 FROM @s1;
        EXECUTE stmt1;
        SET @sql = CONCAT('SELECT IF(EXISTS(SELECT 1 As Result  FROM Answer WHERE QuestionId = ',
        @QuestionId,' AND (');
        SET @countcor := 0;
        SET @countfal := 0;
  OPEN curAnswers;

  getsql: LOOP
      FETCH curAnswers INTO _int;
      IF finished = 1 THEN 
          LEAVE getsql;
      END IF;
      -- build query
      SET @sql2 := CONCAT(@sql,_int
      ,' IN (SELECT Id FROM Answer WHERE QuestionId = ',@QuestionId,' AND CorrectAnswer = 1)              )),@countcor := @countcor +1,@countfal := @countfal +1)' );
      PREPARE stmt1 FROM @sql2;
                EXECUTE stmt1;
  END LOOP getsql;
  CLOSE curAnswers;
SET NoCorrectAnswer := @countcor;
SET NoFalseAnswer := @countfal;
END
CALL CheckValid_Answer(1,'1,3',@OK, @false);
| IF(EXISTS(SELECT 1 As Result  FROM Answer WHERE QuestionId = 1 AND (1 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1)             )),@countcor := @countcor +1,@countfal := @countfal +1) |
| -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------: |
|                                                                                                                                                                                                      1 |

| IF(EXISTS(SELECT 1 As Result  FROM Answer WHERE QuestionId = 1 AND (3 IN (SELECT Id FROM Answer WHERE QuestionId = 1 AND CorrectAnswer = 1)             )),@countcor := @countcor +1,@countfal := @countfal +1) |
| -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------: |
|                                                                                                                                                                                                      1 |
SELECT @OK, @false;
@OK | @false
--: | -----:
  1 |      1

db<>fiddle here

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.