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

问题描述

我试图实现一个多选题测验，并希望将所有我的问题和答案存储在SQLite数据库中。我会有很多问题，对于每个问题，将有2个或更多可能的答案显示。

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. 作为（多对多）

问题（ questionID ：int，questionString：String，correctAnswerID：int）

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

答案（ answerID ：int，answerString：String）

answers (answerID:int , answerString:String)

questions_and_answers（ questionID >）

questions_and_answers (questionID, answerID)

2。

问题（ questionID ：int，questionString：String ，correctAnswerID：int）

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

答案（ answerID ：int，answerString：String，questionID：int foreign key ）

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

我不知道哪一个更好，还是有另一种方式？

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

可能 questions_and_answers 会变得非常大，导致检索时间和内存问题较长？再次，我假设 question_and_answers 将在主键上建立索引。在第二个模式中， answer 将在 answerID 上索引，而不是 questionID ？意味着搜索时间会随着整个表格的搜索而增加？

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?

可能有〜10,000 - 20,000个答案。 （测验可能在移动设备上运行，并且问题需要即时显示）

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")

注意：我不期望有很多重叠的答案问题。考虑到 questions_and_answers 表所需的额外空间，我不认为重叠的数量意味着存储更少的数据

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.

沿着你想要编辑答案的路。对于模式1，这意味着首先搜索该答案是否已经存在。如果它确实存在，那么你必须检查是否有任何问题仍然依赖于旧的答案。如果它不存在，您仍然必须检查是否有任何其他问题依赖于该答案，然后编辑该答案或创建一个新的答案。

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.

模式1只是使生活真的很难。

Schema 1 just makes life really hard.

要回答你的索引问题，你需要在questionId上添加一个索引。一旦你有了这个索引，查找一个问题的答案应该是缩放。

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.

现在，一个完全不同的说明，为什么使用数据库？ 考虑以标准格式（如json）将它们存储为简单文档。每当你查询一个问题，你几乎总是想要的答案，反之亦然。

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.

如果您发现需要更多高级存储（查询，冗余等），您可以移动到如MongoDB或CouchDB的文档数据库。

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

这篇关于在数据库中存储多项选择测验 - 决定模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！