Sql-server – Database design for an exam

database-designsql server

I am designing database for an exam, and I am stuck. I do not know how to do it.

Here is the relevant information:

  • Student answers 10 questions.
  • Each question has 3 options (answers) and student chooses one.
  • Only one answer is correct, other 2 are wrong.
  • Students can take this exam only once. They can not try again.
  • There will be no other exams, this is the only one.

I need help with database design.

What I have tried so far on my own:

Table Students

ID bigint (primary key, identity)
Name nvarchar(MAX)

Table Questions

ID bigint (primary key, identity)
TextOfTheQuestion nvarchar(MAX)

Table Answers

ID bigint (primary key, identity)
TextOfTheAnswer nvarchar(MAX)
QuestionID bigint (foreign key to Questions.ID)
isCorrectAnswer bit

Table StudentChoices

StudentID bigint (primary key, foreign key to Students.ID)
AnswerID bigint (primary key, foreign key to Answers.ID)

This is my personal design for learning purposes. I am trying to learn Entity framework + C# on my own.

Best Answer

Easy on the bigint and nvarchar(MAX) 

Table Student:

ID int (primary key, identity)
Name nvarchar(800)

Table Question:

ID smallint (primary key, identity)
TextOfTheQuestion nvarchar(800)
CorrectAns tinyint FK (to AnswerNum.Num)  
tested and can make a composite FK to Answer  QuestionID, AnsNum   
I was not sure could do this but it would mean you have to create the question  
Then populate Answer  
Then come back and edit question for CorrectAns so may not be the way you want to go

Table Answer:

QuestionID smalling PK FK (to Question.ID) 
AnswerNum tinyint   PK FK (to AnswerNum.Num)
TextOfTheAnswer nvarchar(800)


Table StudentChoice:

StudentID int       (primary key, foreign key to Student.ID)
QuestionID smallint (primary key)
AnswerNum tinyint 
(QuestionID, AnswerNum) REFERENCES Answer (QuestionID, AnswerNum)

Table AnswerNum:

Num tinyint PK 
-- just values 1, 2, 3
-- this way you can change number of questions in the future

This is how you score
When the primary query comes out clean it is sign of a good db design 

  select Student.Name, count(*) as score 
  from student
  left join StudentChoice
         on Student.ID = StudentChoice.StudentID 
  left join Question
         on Question.QuestionID = StudentChoice.QuestionID 
        and Question.CorrectAns = StudentChoice.AnswerNum   
            -- CorrectAns in Question makes this a whole lot easier  
  group by Student.Name
Related Question