如何在SQL Server中存储段落类型的问题和答案？ [英] How to store passage type questions and answers in SQL server ?

问题描述

I am started doing a project :- Online Exam in ASP.NET with C#, database:- SQL Server

The exam consists of different sections such as Reasoning, Quantitative Aptitude & Numerical ability, English and Mathematics.
The exam is of objective type like fill in the blacks, option type and passage type.

The problem is how do I store passage type questions and answers in table??Because it will contain one long passage and based on that more than one questions will be asked with multiple choice options.

我尝试过：



问题表由以下列组成：

Qid，Question，Option1，Option2，Option3，Option4，correctAnswer

What I have tried:

The question table consist of the following columns:
Qid,Question,Option1,Option2,Option3,Option4,correctAnswer

推荐答案

如果我理解你的问题，那就是如何存储组问题之前的段落。

如果是这样，我的方式就是有一个带有Id（整数）和Text（varchar）列的附加Passage表;在Question表中有一个附加列，它是Passage表的外键（对于独立的问题可能是可空的。）

If I understand your problem, it is how to store the "passage" that precedes a group of questions.
If so, the way I'd do it would be to have an additional Passage table with Id (integer) and Text (varchar) columns; and have an additional column in the Question table that is the foreign key to the Passage table (probably nullable for the questions that are standalone).

响应你对Solution 1的评论 - 如果我这样做我可能有三张桌子，即

Responding to your comment to Solution 1 - If I was doing this I would probably have three tables, namely

* passage
* answer
* question

这样我可以重复使用（不正确）一些问题选项的答案 - 所以我只存储一次文本。每个表都应该有自己的Id列，问题表将需要外键到其他两个。例如。

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,		-- set this up as a FK to table [passage]
	question varchar(max),
	option1 int,		-- set these up as a FKs to table [answer]
	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)

请注意，我已经将文本重复用于答案3和5，但仅以存储整数值为代价。



获取数据将涉及多个连接，例如

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

（所以你可能想把索引放在每张桌子的Id列上）



如果你想重复使用不同段落的问题文本那么你需要另一个链接问题的表在一个段落...这个新表将包含一个问题的链接，所有选项外键和正确的答案。问题表将只包含其Id和文本列。

(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.

如果您使用的是现代版本的SQL，则可以使用varchar（max）

If you're using a modern version of SQL you can use varchar(max)

这篇关于如何在SQL Server中存储段落类型的问题和答案？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！