How to design DB for question-answer (MySql)

Question

I need to design a DB for a forum. I am separating the root post from it's sub-posts for various reasons. I need the text the user enters to be optimally search-able, also from performance point of view.

My question, should I separate each table (root-posts and sub-posts) into two tables:
root-posts_meta (hold data such as id,creation time,views,....)
root-posts_data (id,title,body) indexed with full-text

The same idea with the sub-posts table.

Thanks.

Answer 1

The separation isn't going to affect its searchability or search performance. If that's your only concern, you may as well leave each as a single table.

Answer 2

TEXT fields are stored out of row anyway.

Separating the tables will improve neither readabitily nor performance of your queries.

You better keep it in one table.

Answer 3

As the others have said, don't separate the tables. It has no benefit, and it actually has the disadvantage of performance. Adding another table means it's just another table join your query has to do when rendering a page.

Answer 4

When I made simmilar thing, I've put thread data in one table, and post data (including root posts) in other. Before answering you question, I must ask you, are you really sure you need to separate root and sub?

If you want to stick with root-sub separation, I don't think you will gain anything by separating those even further.

Answer 5

Basically root messages and sub messages are pretty much the same thing in nature in a regular forum application. If you really want to have some special information about the start of a new thread, you might want to have a seperate table called thread, and all messages belonging to that thread in the message table. The messages themselves can have a parent_msg_id of null for root messages, or the id of another message if they are replies. Like this:

thread:
- thread_id
- started_ts
- author (long live redundancy!)
- other columns

message:
- message_id
- thread_id (reference to thread-thread_id)
- parent_msg_id (nullabel reference to message.message_id)
- body, author, timestamp etc

Answer 6

Normalizing is about splitting the data up into smaller pieces, which creates a better design. Unfortunately separate tables means more joins and joins are bad for performance. So, you end up de-normalizing your schema to improve performance later.

I would suggest keeping those things in the same table.

Only put things in different tables if they really are completely different, not just slightly different or you feel like it would be nice to separate them.

Answer 7

Since InnoDB has no FULLTEXT support, and if some kind of transaction support is needed, then there is no way around this separation.
mysql-fulltext

Elaborate explanation: InnoDB does not have full text, MyIsam has no TX support. Take for example SO. Each question entity has number of votes, users updating it, history of changes (In my system I have many other things, let's not go into the business logic of what I do). Many of those fields has to change over the life time of the entity in conjunction with other changes in other tables (i.e. changes under one transaction), And I need the full-text support on the data fields.

Answer 8

If transaction support is important to you, then you could still use one table for data and have something like Sphinx for fulltext search.