主页 / dba / 问题 / 142282
Accepted
AlwaysLearningNewStuff
Asked: 2016-06-27 05:21:19 +0800 CST

考试数据库设计

  • 772

我正在为考试设计数据库，但我被卡住了。我不知道怎么做。

以下是相关信息：

  • 学生回答 10 个问题。
  • 每个问题有 3 个选项（答案），学生选择一个。
  • 只有一个答案是正确的，其他2个是错误的。
  • 学生只能参加一次此考试。他们不能再试一次。
  • 不会有其他考试，这是唯一的考试。

我需要数据库设计方面的帮助。

到目前为止我自己尝试过的：

表学生

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

表问题

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

表答案

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

表学生选择

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

这是我个人出于学习目的而设计的。我正在尝试自己学习实体框架 + C#。

sql-server database-design

6 个回答

  1. Best Answer

    轻松使用 bigint 和 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

    这就是你得分的方式
    当主查询干净时，它是良好数据库设计的标志 

      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
    • 6

  2. 我认为你已经在你的初始模式中非常确定了。

    但是，由于我们正在处理 MCQ 场景，因此您不需要“答案”表 - 答案可以包含在问题中（连同两个不正确的答案 - 见下文）。这将大大简化事情。我建议的另一个较小的更改是PRIMARY KEY在 Question 表中使用 Question_ID 字段。

    我在下面准备了一个模式——恐怕是 MySQL，我不经常使用 Microsoft SQL Server，也没有实例可用。“翻译”应该不会太难。

    几点评论。

    我建议您不要只在每个表中使用“ID”作为字段名称，而是使用“Table_Name_ID”——它可以让你的 SQL 更清晰，也有助于调试——如果你收到类似“...... ID 错误”的消息on insert..." - 您不知道消息指的是哪个表的 ID，而如果您明确命名它们，则错误消息将变得有意义。

    CREATE TABLE Student (Student_ID int, Name VARCHAR(25));
CREATE TABLE Question (Question_ID int, Name VARCHAR(25), Question_Text 
                       VARCHAR(64000), Answer1 VARCHAR(4096), 
                       Answer2 VARCHAR(4096), Answer3 VARCHAR(4096),
                       Correct_Answer TINYINT);
CREATE TABLE Student_Response (Student_ID int, Question_ID int,
                               Student_Answer TINYINT);

    如果您将问题表述为标记为“A”、“B”或“C”，则需要将 TINYINT 修改为 CHAR(1)。

    你会注意到我为我FOREIGN KEY的 s &c 使用了相对较长且（希望）易于理解的名称。有两个原因：

    • 程序员不必去挖掘文档来找出一个实体是什么——它是不言自明的，

    • 对于调试来说绝对必要——这是绝大多数应用程序花费大部分时间的地方。收到“...约束 SYS00003433 已被违反...”的消息并不是很有帮助。Oracle 非常适合为未命名的约束提供其自己的“特殊”系统生成名称。

    您还会注意到我使用单数表名。我建议你也这样做——这样你就不必考虑“嗯……是学生还是学生？” 无论哪种方式，选择一个标准并坚持下去。

    如果你用谷歌搜索“数据库设计最佳实践”，你会得到任意数量的网站——花时间和精力去阅读其中的一些，看看共识是什么——如果很多人认为这是个好主意，那可能是！

    ALTER TABLE Student_Response 
  ADD CONSTRAINT fk_sr_student FOREIGN KEY (Student_ID) 
    REFERENCES Student (Student_ID);
ALTER TABLE Student_Response 
  ADD CONSTRAINT fk_sr_question FOREIGN KEY (Question_ID) 
    REFERENCES Question (Question_ID);

    这种独特的约束是为了消除对同一个问题有两个答案的可能性。

    ALTER TABLE Student_Response 
  ADD CONSTRAINT uq_sr_student_question 
    UNIQUE (Student_ID, Question_ID);

    即使您特别排除它，添加一个考试（相同的考试，不同的时间）表或可能为此添加一个“尝试”表，然后为不同的考试创建一个考试表应该相对容易 - 即数据库 101、数据库 201。

    也许您应该考虑与尝试相关的日期甚至日期时间字段 - 在早上和下午参加相同的考试？

    在一天结束的时候，一个人可以设计直到奶牛回家。避免强迫性的设计混乱——我的意思是（提前）选择一个你会对系统功能感到满意的点并停在那里。我知道陷入不断添加“零碎”而没有完成任何实际工作的陷阱是多么容易。

    • 5

  3. “设计数据库”考试任务解决方案很可能应该展示学生如何通过 DB 数据完整性工具（PK、FK）来表达领域业务规则。

    您的解决方案涵盖了其中的大部分。缺少的规则是：
    （1）只有一个答案是一个问题的解决方案。如果没有触发器或额外的应用程序代码，位标志将无济于事。
    (2) 学生只能选择一个问题的答案。 

    Table Students:

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

Table Questions:

ID int (primary key, identity)
TextOfTheQuestion nvarchar(4000)
SolutionID int
foreign key (SolutionID) to Answers(ID)

    我添加了SolutionID，这样Answer 就只有一个解决方案。

    Table Answers:

ID int (primary key, identity)
QuestionID int (foreign key to Questions.ID)
TextOfTheAnswer nvarchar(4000)

    要满足 (2) StudentChoices 的 PK 必须是 (StudentID,QuestionID) 这意味着学生回答了问题并且他的答案是......。

    Table StudentChoices:

StudentID int (primary key, foreign key to Students.ID)
QuestionID int (primary key)
AnswerID int

    现在出现一个问题，AnswerID 和 QuestionID 不能相互矛盾，即 AnswerID 必须属于 QuestionID 引用的 Question。我们可以通过在 StudentChoices 声明 FK 来实现它：

    foreign key (QuestionID, AnswerID) to Answers(QuestionsID, ID)

    为了能够在上述 FK 中定位 Answers(QuestionsID, ID)，这对属性必须在 Answers 中声明为 Unique：

    unique (QuestionID,ID) -- target of FK from Questions, StudentChoices

    声明这样的 Unique 绝对省事，因为 PK 的每个超集都是唯一的。使用相同的唯一性，我们还通过将 Questions 处的 FK 替换为

    foreign key (ID,SolutionID) to Answers(QuestionsID, ID)
    • 2

  4. 当我查看您现在拥有的结构时，我会将标识列从 BigInt 更改为 Int。我建议您查看您期望有多少学生、问题、答案和选择，并选择具有适当大小范围的数据类型。

    我也会用一个更合理的字段替换 nvarchar(max) 。由于 nvarchar(max) 上的函数存在限制。我只会把它作为最后的手段。

    至于表结构本身，我将对学生选择表进行更改。

    1. 我会添加一个 Identity 列作为主键来唯一标识一行。这使应用程序的生活更轻松。

    2. 我会添加一个问题 ID 列。这样您就可以看到学生 A，对于问题 B，选择了答案 C。

    3. 可选：如果您在学生 ID 和问题 ID 上添加了唯一索引，则学生每个问题只能选择 1 个答案。

    其他需要考虑的事情：这个数据库会被用于许多不同的考试吗？（考虑考试编号）或者一个学生可以参加两次相同的考试吗？你会怎么处理？

    • 1

  5. 正如其他人提到的，您应该使用合理的数据类型，例如BIGINT学生/问题不使用（永远不会超过 20 亿学生），NVARCHAR(MAX)问题/答案可能不使用。您还应该NOT NULL尽可能添加约束。

    一个干净的逻辑数据模型将导致：

    表学生

    StudentID int (primary key, system assigned?)
-- of course there will be an official registration number 
-- issued by the univerity which is probably not based on a sequence
Name nvarchar(200) not null
-- of course there will be lots of other columns like last_name/first_name/birthdate/...

    表问题

    QuestionID smallint (primary key, system assigned?) 
TextOfTheQuestion nvarchar(4000) not null

CorrectAnswer tinyint not null check (CorrectAnswer between 1 and 3)
-- for a 100% clean model you might:
-- drop the check, remove the NOT NULL 
-- and do a foreign key to Answers(QuestionID, AnswerID) instead
-- But then you got a circular reference and this is hard to load

    表答案

    QuestionID int (foreign key to Questions(QuestionID))
AnswerID tinyint check (AnswerID between 1 and 3)
TextOfTheAnswer not null nvarchar(4000)
Primary key (QuestionID, AnswerID)

    表学生选择

    StudentID int (foreign key to Students.StudentID)
QuestionID smallint (foreign key to Answers.AnswerID)
AnswerID tinyint -- NULLable as student might not have answered question (yet)?
Primary key (StudentID, QuestionID)

    然后获取（正确）已回答问题的查询将如下所示：

    select s.Name, 
   count(sc.AnswerID) as AnsweredQuestions
   count(q.AnswerID) as CorrectAnswers
from student as s
left join StudentChoice as sc
       on s.StudentID = sc.StudentID 
left join Question as q
       on q.QuestionID = sc.QuestionID 
      and q.CorrectAns = sc.AnswerId
group by Student.Name

    当然，这可能会变得越来越复杂，例如关于实际问题（按复杂性升序排列）：

    • 正好有10个问题吗？
    • 还是有多组 10 个问题？
    • 还是从一大堆问题中随机选择 10 个问题？
    • 1

  6. 我不会解决问题的特定数据模型方面（已经有五个答案可以很好地解决这个问题），而是解决这个问题的既定目标（毕竟，上下文很重要）。

    这是我个人出于学习目的而设计的。我正在尝试自己学习实体框架 + C#。

    现在，我确实明白，很可能还有其他尚未共享的因素和细节使得追求这个特定的数据模型成为最合适的行动方案。但是，我只知道共享的内容（上面的声明），因此请记住：

    如果您使用此模型只是为了学习实体框架 (EF)、C# 以及可能的 MVC / ASP.NET / 等，那么考虑以下方法之一可能会更好地为您服务：

    1. 鉴于您已经提出了几个数据建模问题，使用与这些问题相关的实际数据模型可能会更容易专注于学习 .NET 内容。这个特定问题中的数据模型是人为的情况，因此尚未使用真实数据和真实用户进行测试；它缺乏某些随着系统开始被使用而浮出水面的复杂性。因此，当您应该专注于如何使用实体框架与您希望已经熟悉/熟悉的现实业务规则进行交互时，您可能会花费相当多的时间重新思考该模型的各个方面和。

    2. 如果您确实需要/想要一个系统来管理参加考试的学生，那么当许多此类系统已经存在并且可以研究时，无需重新发明轮子。有一整类软件称为学习管理系统 (LMS)，其中至少有一个是开源的并且非常受欢迎，因此它们已经经历了最初的成长阵痛，即寻找不起作用的小东西。而且您当然不需要完整的数据模型，因为它处理的内容比您学习 EF 所需的要多得多，因此只需获取开始围绕它构建 .NET 应用程序所需的部分。

      我正在考虑的系统称为 Moodle，可以在 GitHub 上找到：
      https
      ://github.com/moodle/moodle/blob/master/lib/db/install.xml 该链接直接指向他们的数据模型。它实际上是对数据模型的描述，然后他们将其呈现为 6 种左右不同的 RDBMS 方言之一。但它很容易阅读，您可以轻松查看表、索引、关系等。您甚至可以安装 Moodle，以便它创建该模型的 SQL Server 版本。他们的代码都是 PHP 的，所以你可能需要单独下载，但他们确实有Windows 的二进制文件（请务必阅读左侧的“我选择哪个版本？”部分）。

    同样，我了解您可能有一个或多个理由需要坚持使用您在本问题中询问的数据模型。因此，这些只是未来（如果不是现在）要考虑的要点，或者可能是其他搜索“考试/测试数据模型”的人可能不知道开源选项（例如 Moodle）的要点。

    但是，无论您选择哪条具体路径，由于您使用的是 ORM（即实体框架），因此您肯定还需要了解更多关于性能调整、各种数据类型的优缺点等方面的知识。请务必查看“计划缓存膨胀”，并记住，对于每个对象和操作，EF 确实可以选择调用存储过程，而不是动态生成 SQL :-)。

    • 1

相关问题