在数据库中存储一个多选题测验--决定模式[英] Storing a multiple choice quiz in a database - deciding the schema

本文是小编为大家收集整理的关于在数据库中存储一个多选题测验--决定模式的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在尝试实现多项选择测验,并希望将我的所有问题和答案存储在SQLITE数据库中.我将有很多问题,对于每个问题,都会显示2个或更多可能的答案.

我的问题是,我应该如何将问题和答案存储在数据库中?我有两个架构的想法(BOLD中的主要键)

  1. AS(许多人)

问题( QuestionId :int,Question String:string,criviceanswerid:int)

答案(答案:int,answers tring:string)

问题_and_answers( QuestionId , andersID )

2.

问题( QuestionId :int,Question String:string,criviceanswerid:int)

答案(答案:int,答案启动:string,QuestionId:int 外键)

我不确定哪个更好,或者是否有另一种方法?

也许questions_and_answers会变得很大,并导致长时间的检索时间和记忆问题吗?再说一次,我假设question_and_answers将在主键上索引.在第二个模式中,answers将在answerID上进行索引,而不是questionID?这意味着搜索时间会上升,因为必须搜索整个表格?

可能有〜10,000-20,000个答案. (测验可以在移动设备上运行,需要"立即"显示问题)

注意:我不希望在问题之间有太多的答案重叠.考虑到questions_and_answers>表

的额外空间

推荐答案

您是第二个架构,因为它为实际域进行建模:每个问题都有一组答案.即使您可以通过存储重复的答案来"压缩"数据,也与实际域不匹配.

在道路上,您将要编辑答案.使用模式1,这意味着首先搜索该答案是否已经存在.如果确实存在,那么您将必须检查是否仍然依靠旧答案.如果不存在,您仍然必须检查是否有其他问题依赖该答案,然后编辑该答案或创建新答案.

模式1使生活真的很难.

要回答您的索引问题,您需要在问题上添加索引.一旦有了该索引,查找问题的答案就应该扩展.

现在,在完全不同的音符上,为什么要使用数据库? 考虑将它们存储为简单文档以JSON等标准格式.每当您询问问题时,您几乎总是想要答案,反之亦然.您可以在一个步骤中加载整个文档,而不是执行多个查询.

如果您发现您需要更多的高级存储(查询,冗余等),则可以移至诸如mongodb或couchdb之类的文档数据库.

其他推荐答案

似乎死锁(圆环) QuestionId 列被称为答案中的外键 table和 recriptanswerid 列被转介作为问题中的外键表.

最好在答案表中创建一些类型的列来标记正确的答案并删除 recriptanswerid 列.

本文地址:https://www.itbaoku.cn/post/597735.html

问题描述

I am trying to implement a multiple choice quiz and will want to store all my questions and answers in a SQLite database. I will have many questions, and for each question there will 2 or more possible answers to display.

My question is, how should I store the questions and answers in a database? I have two ideas for a schema (primary key in bold)

  1. as (many to many)

questions (questionID:int , questionString:String, correctAnswerID:int)

answers (answerID:int , answerString:String)

questions_and_answers (questionID, answerID)

2.

questions (questionID:int, questionString:String, correctAnswerID:int)

answers (answerID:int, answerString:String, questionID:int foreign key)

I'm not sure which one is better, or if there is another way?

Maybe questions_and_answers would get very large and cause long retrieval times and memory problems? Then again, I assume question_and_answers would be indexed on the primary keys. In the second schema, answers would be indexed on answerID and not questionID? meaning the search times would go up as the whole table would have to be searched?

There may be ~10,000 - 20,000 answers. (the quiz may be run on a mobile device and questions will need to be shown "instantly")

Note: I don't expect there to much overlap of answers between questions. I wouldn't think the amount of overlap would mean less data being stored, considering the extra space required by the questions_and_answers table

推荐答案

You're second schema is the better one, because it models the actual domain: each question has a set of answers. Even if you can "compress" the data by storing duplicate answers once, it does not match the actual domain.

Down the road you'll want to edit answers. With schema 1, that means first searching if that answer already exists. If it does exist, you then would have to check if any questions still rely on the old answer. If it did not exist, you would still have to check if any other questions relied on that answer, and then either edit that answer in place or create a new answer.

Schema 1 just makes life really hard.

To answer your index questions, you would need to add an index on questionId. Once you have that index, looking up answers for a question should scale.

Now, on a completely different note, why use a database for this? Consider storing them as simple documents in a standard format like json. Anytime you query a question, you will almost always want the answers, and vice versa. Instead of executing multiple queries, you can load the entire document in one step.

If you then find you need more advanced storage (queries, redundancy, etc) you can move to a document database like MongoDB or CouchDB.

其他推荐答案

It seems deadlock (circular loop) as questionID column is referred as foreign key in answers table and correctAnswerID column is referred as foreign key in questions table.

It's better to create a bit type column in answers table to marked the correct answer and remove correctAnswerID column.