Responding to your comment to Solution 1 - If I was doing this I would probably have three tables, namely
* passage
* answer
* question
That way I can re-use (incorrect) answers for some of the question options - so I am only storing the text once. Every table should have its own Id column and the question table will need foreign keys to the other two. E.g.
create table passage
(
passageId int identity(1,1),
passage varchar(max)
)
create table answer
(
answerId int identity(1,1),
answerText varchar(max)
)
create table question
(
id int identity(1,1),
passage int,
question varchar(max),
option1 int,
option2 int,
option3 int,
option4 int,
correct int
)
An example of how this could work:
insert into passage values
('This is a long passage for evaluation'),
('This is another long passage for evaluation')
insert into answer values
('An anwer that can be reused'),('Another answer'),
('And yet another one'),('and another answer'),
('the correct answer for Q1'),('the correct answer for Q2')
insert into question values
(1, 'Q1 text', 1,2,3,5,5),
(2, 'Q2 text', 3,4,5,6,6)Note that I've re-used the text for answers 3 and 5 but only at the cost of storing an integer value.
Getting the data out will involve multiple joins e.g.
select p.passage, q.question,
a1.answerText as Option1, a2.answerText as Option2,
a3.answerText as Option3, a4.answerText as Option4,
c.answerId as Correct
from passage p
join question q on p.passageId = q.passage
join answer a1 on q.option1 = a1.answerId
join answer a2 on q.option2 = a2.answerId
join answer a3 on q.option3 = a3.answerId
join answer a4 on q.option4 = a4.answerId
join answer c on q.correct = c.answerId
(So you would probably want to put indexes on the Id columns on each table)
If you want to re-use the question text for different passages then you would need another table that links a question to a passage... this new table would contain a link to the question, all of the options foreign keys and the correct answer. The question table would then only contain its Id and text columns.
Submit an article or tip