relational database: how to design this table

Question

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.

As you can see, there are many questions here and each can have many answers.

1. How should I store the answers in a table?
2. 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?
3. What if there are 100,000 answers like there is here? Do I still store them in 1 table?
4. What keys should I use to minimize search time when I want to search for the answers of a specific question?

The database is both read and write if that makes any difference in this case.

Answer 1

Doing a google search brings up: http://sqlserverpedia.com/wiki/Understanding_the_StackOverflow_Database_Schema and http://blog.stackoverflow.com/2008/09/what-was-stack-overflow-built-with/

Answer 2

1. Far too broad. You are basically asking how to do database design. Get a book.
2. Yes, you should store all the answers in one table.
3. 100,000 is not a particularly large number.
4. 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.

Answer 3

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:

questions: question_id, question_text answers: 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=?".

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.