How would you structure a forum's DB schema? [

问题:

I'm building a little forum for practice. I see that forums like phpBB store the thread text in a separate table.

Why? Why not store it all in the same table?

Something like:thread_id, thread_date, thread_text, thread_author

Why is it done this way? How would you do it?

回答1:

I found this cool schema online.

schema http://yensdesign.com/tutorials/forumsdatabase/schemaFull.jpg

seems pretty good starter for the database schema of any basic forum.

I know this is old, but figured I'd post this for anyone who's gonna find this question again.

回答2:

I don't actually know why this is done, but one reason I can imagine is optimizing search and retrieval for the post metadata (date, author, etc.).

According to Joel (and Joel is always right! ;-) databases store their data in fixed-length fields composing fixed-length records, so it's easy to jump from one row to the next just by incrementing a pointer by the byte length of a record. But large text fields used to store post text can't have a fixed size, because the length of a post varies over a wide range and creating fixed-length storage large enough to hold all posts would waste tremendous amounts of space. That means storing the post text in the same table as the other information would make it a lot slower when you want to retrieve the metadata for large numbers of posts, as is done every time somebody views the main forum page.

The way to get the best of both worlds is to put the fixed-length fields (i.e. everything except the post text) in one table and the variable-length fields (i.e. the post text) in another.

回答3:

Never looked inside the phpBB guts, but perhap it is because of full-text indexing. Inno-db engine for the main table to allow transaction and what not. MyIsam for full-text indexing.

回答4:

For one thing, the filesystem layout of most relational databases is such that storing large blocks of arbitrary text or data can slow down the system. Since data is usually stored by row, when doing searches the database now has to skip over variable-length text fields even when looking for unrelated fields.

Second, putting everything in one table makes it much harder to add to the data model later on, if you need more data for each thread_id, for instance.

Designing database schemas well requires some education. You should start with http://en.wikipedia.org/wiki/Database_normalization. Be sure to understand third-normal form.

回答5:

InnoDB doesn't support FULLTEXT indexing and MyISAM doesn't support transactions.

Don't know phpBB, but probably that's why they separate the tables.

回答6:

They do not store text in the same table because of the size the table can reach.

This way, even with a very large number of entries, the thread list table is small, well indexed and it's fast to scan it. The text is accessed only when necessary, using a primary key, which is fast too.

For small forums, I think this is not necessary, since there is a little coding overhead.

回答7:

In addition to Julien's excellent answer, it is quite common to move posts to other threads (by say an admin or moderator). Having the text in a "post table" helps support this.