I'm a database newbie designing a database. I'll use SO as an example because it's easier to ask it on something that you can see already, but it's not the same, it will just help me understand the right approach.
我是数据库新手设计数据库。我将使用SO作为一个例子,因为它更容易在你已经可以看到的东西上问它,但它不一样,它只会帮助我理解正确的方法。
As you can see, there are many questions here and each can have many answers.
如您所见,这里有很多问题,每个问题都有很多答案。
- How should I store the answers in a table?
- Should I store all the answers in the SAME table with a unique id (make it the key) and just a new field for the question id?
- What if there are 100,000 answers like there is here? Do I still store them in 1 table?
- What keys should I use to minimize search time when I want to search for the answers of a specific question?
我该如何将答案存储在表格中?
我应该将所有答案存储在SAME表中,并使用唯一的ID(使其成为键),并且只是问题ID的新字段吗?
如果有像这样的100,000个答案怎么办?我还把它们存放在一张桌子里吗?
当我想搜索特定问题的答案时,我应该使用哪些键来最小化搜索时间?
The database is both read and write if that makes any difference in this case.
如果在这种情况下有任何不同,数据库都是读写的。
3 个解决方案
#1
0
You shouldn't worry too much about normalization and scaling initially -- just get the problem laid out in a clear way so you can try things out and see how it goes. The "standard" approach to a Q&A-type schema would be to have a table for questions, and a table for answers. Each answer belongs to one question, and therefore you'd have a question_id in the answers table which will point to its question. You could (and likely should) create an index on that column in the answers table to help optimize lookups. So for example you'd probably start with:
你最初不应该过于担心规范化和扩展 - 只需要以明确的方式解决问题,这样你就可以尝试一下并看看它是如何进行的。 Q&A类型架构的“标准”方法是提供问题表和答案表。每个答案都属于一个问题,因此您在答案表中会有一个问题,该问题将指向其问题。您可以(并且可能应该)在答案表中的该列上创建索引,以帮助优化查找。例如,你可能会开始:
questions: question_id, question_text answers: answer_id, question_id, answer_text
问题:question_id,question_text答案:answer_id,question_id,answer_text
And to get the answers for a question, you'd simple "select answer_text from answers where question_id=?".
要获得问题的答案,您可以简单地“从answer_id =?的答案中选择answer_text”。
Hope that helps as a starting point. There are different approaches if you start to approach truly huge numbers of entries, but as Marcelo mentioned above, 100k entries is really very small for a modern database.
希望有助于作为一个起点。如果你开始接近真正大量的条目,有不同的方法,但正如Marcelo上面提到的,对于现代数据库来说,100k条目真的非常小。
#2
2
- Far too broad. You are basically asking how to do database design. Get a book.
- Yes, you should store all the answers in one table.
- 100,000 is not a particularly large number.
- Each answer is associated with a single question and so it should have a foreign key on the primary key of the question table. Searching is as simple as restricting on that key.
太宽泛了。您基本上是在询问如何进行数据库设计。拿一本书。
是的,您应该将所有答案存储在一个表中。
100,000不是特别大的数字。
每个答案都与一个问题相关联,因此它应该在问题表的主键上有一个外键。搜索就像限制该密钥一样简单。
#3
1
Doing a google search brings up: http://sqlserverpedia.com/wiki/Understanding_the_*_Database_Schema and http://blog.*.com/2008/09/what-was-stack-overflow-built-with/
正在进行谷歌搜索:http://sqlserverpedia.com/wiki/Understanding_the_*_Database_Schema和http://blog.*.com/2008/09/what-was-stack-overflow-built-with/
#1
0
You shouldn't worry too much about normalization and scaling initially -- just get the problem laid out in a clear way so you can try things out and see how it goes. The "standard" approach to a Q&A-type schema would be to have a table for questions, and a table for answers. Each answer belongs to one question, and therefore you'd have a question_id in the answers table which will point to its question. You could (and likely should) create an index on that column in the answers table to help optimize lookups. So for example you'd probably start with:
你最初不应该过于担心规范化和扩展 - 只需要以明确的方式解决问题,这样你就可以尝试一下并看看它是如何进行的。 Q&A类型架构的“标准”方法是提供问题表和答案表。每个答案都属于一个问题,因此您在答案表中会有一个问题,该问题将指向其问题。您可以(并且可能应该)在答案表中的该列上创建索引,以帮助优化查找。例如,你可能会开始:
questions: question_id, question_text answers: answer_id, question_id, answer_text
问题:question_id,question_text答案:answer_id,question_id,answer_text
And to get the answers for a question, you'd simple "select answer_text from answers where question_id=?".
要获得问题的答案,您可以简单地“从answer_id =?的答案中选择answer_text”。
Hope that helps as a starting point. There are different approaches if you start to approach truly huge numbers of entries, but as Marcelo mentioned above, 100k entries is really very small for a modern database.
希望有助于作为一个起点。如果你开始接近真正大量的条目,有不同的方法,但正如Marcelo上面提到的,对于现代数据库来说,100k条目真的非常小。
#2
2
- Far too broad. You are basically asking how to do database design. Get a book.
- Yes, you should store all the answers in one table.
- 100,000 is not a particularly large number.
- Each answer is associated with a single question and so it should have a foreign key on the primary key of the question table. Searching is as simple as restricting on that key.
太宽泛了。您基本上是在询问如何进行数据库设计。拿一本书。
是的,您应该将所有答案存储在一个表中。
100,000不是特别大的数字。
每个答案都与一个问题相关联,因此它应该在问题表的主键上有一个外键。搜索就像限制该密钥一样简单。
#3
1
Doing a google search brings up: http://sqlserverpedia.com/wiki/Understanding_the_*_Database_Schema and http://blog.*.com/2008/09/what-was-stack-overflow-built-with/
正在进行谷歌搜索:http://sqlserverpedia.com/wiki/Understanding_the_*_Database_Schema和http://blog.*.com/2008/09/what-was-stack-overflow-built-with/