Shared Purpose tables in database design ( how would *you* implement a question/answer facility )

Answer 1

I think that from the perspective of SO, both questions and responses are the same thing -- user posts. They just happen to be related. If a post has no parent, then it's a question. If a post does have a parent, then it's an answer. I find this perfectly reasonable though I'm not sure I would make the same choice since there are some significant differences. Perhaps these are stored in separate tables though.

I've basically done the same thing in one of my applications. I track events. An event is a "Master" event if it has no parent. If it has a parent, then it has to be a subevent of a "Master" event. They share many of the same base properties and so they share a table. "Master" events have some additional properties that are stored in a separate table. Generally when I'm selecting subevents, I already know the "Master" event and so a separate query is not needed.

Answer 2

We have a similar thing with our student management system which stores applications and enrollments on the same table. An application is in essence a special case of enrollment on a course sort of not active yet. In my case it's probably best to use it this way as an application is easy to upgrade to an enrollment.

Really it depends how you are going to end up querying the data, I suspect a Q and A system is going to have a search facility so this is going to want to search questions and answers so it makes sense to have them in the same table. Also the questions and answers likely require the same extra data such as a date modified, who sent it... Just make sure you have a field in the index of the table defining if it is a question or an answer if you go this route.

It is worth forking when you are having to work around your solution, but remember just because you initially have it as one table it is relatively easy to run a script through separating it off later.