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

Accepted answer

Score: 18

I found this cool schema online.

_{(source: yensdesign.com)}

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

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

Score: 5

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

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

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

Score: 3

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

Score: 3

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

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

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

Score: 2

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

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

Score: 1

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

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

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

Score: 1

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

Source: stackoverflow.com

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

Best practice for storing tags in a database?

Go - get parent struct

Inner class accessing outer class

virtual inheritance from base struct

Represent a class as a dict or list

memcpy of structure having pointers as members in C

What is the size of HANDLE?

When to use a union and when to use a structure

Double alignment

Best algorithm for efficient collision detection between objects