Forum Database design

Opinions wanted on table design: One big table for posts, smaller ones?

[Warboss Alex]

Hey all,

I'm currently designing a second version of my own PHP/MySQL forums. Standard bulletin board stuff, using a database to store the topics, posts, etc.

The site will host several forums, some of which will only be available through a particular page of the site, as well as having a 'master' page listing all forums.

For this reason, I've decided that the database should be split into several tables, one (or more, as applicable) for each forum.
So page x would pull its posts from table x, page y
from tables yy and yyy.. (example).

As far as I know, several smaller tables are better than one big one, for database perfomance, especially as queries don't have to sort through a table to see which posts belong on a particular forum, it'd just dump the whole lot.

What I'm asking, is that has anyone on these forums done something like this before? I'm expecting about a dozen forums, which'd mean twice as many tables (since each forum has to have a 'topics' table and a 'posts' table). Will this be hell to maintain?

I thought I'd have to hard-code which forums belong to each page into the page script itself, but then I realised I could just make this forum list a table, too, each page having an id, and a serialised/imploded list of forums it'd need, and the script would have that id as a parameter, which'd load the list and the correct forums..

Just wondering if anyone has any suggestions, tips or similar experience with doing something like this. Any help would be most appreciated :).

ps - I'm not a newbie PHPer, and this isn't my first forums script. My first one only had two forums, and so I could store all the topics and posts in their own tables, and select only those rows which belonged to a particular forum. But with potentially 10+ forums, I'm thinking this'd seriously slow down the database, and trying to think of a more efficient way to do things..

[carneddau]

Have you looked at any of the php forum scripts that are available? If these don't provide what you need and you really have to write your own version I'd start by looking at how the bigger forums are coded. The more well known scripts have been through a lot of development and will give you clues as to how to build yours.

Cheers

[dcrombie]

If your forums use the same db structure then they should use the same table. If you create an index on the forumid (or equiv.) field then then the overhead of filtering should be negligible. Splitting up the table is a recipe for future nightmares.

[Warboss Alex]

Thanks dcrombie, that's the advice I was looking for. Okay, so say each 'board' (consisting of its component forums) might get as much as 15,000 posts (before pruning/archiving) .. surely the best thing to do would keep each one in its seperate table. (that'd be like 80,000 records.. *I can dream, can't I? ;)* ..) .. surely by then, I'd've wished I kept each board seperate, right?

The best thing would be to have all topics/post of a particular board (set of forums) in their own table, and have a table for each board?

Like table1 for all the data of board1 (for sub-site 1), table2 for all the data of board 2 (for sub-site 2), etc? How about that?

[dcrombie]

You can always separate the forums into separate tables at a later date if it becomes necessary (but it shouldn't with those numbers). The changes required would be trivial.

Think ahead to what you might need to do in the future:

* archive all posts older than 1 year;
* delete all posts by a particular user;
* find all posts that contain a certain phrase or link;
* move a post to another forum;
* etc.

Now look at how easy that is with a single table, and how difficult with multiple.

[Warboss Alex]

Thanks for the advice. I was planning to use seperate tables and UNION statements, but you've probably got more experience than me in saying that 100,000 records in a MySQL database is okay .. :) They'd be text only records of course, no binary data.

Should I give each sub-site their own table, or would sorting out records by forumid -and- 'site id' be okay? perfomance-wise I mean..

[Netizen]

Looking at most forum systems, and having written one myself, I would say go for keeping your forums all in one basket. But, make sure you split up the information so that you can find meta information more easily.

For example, have a forums table with forum name, description, forum id, site id, etc; a threads table with forum id, thread id, user id,thread subject, number of posts in the thread, etc; and posts table with forum id, thread id, post id, user id,subject, body, etc, etc.

Keeping your data normalised like this will mean you can generate listing pages with much less database overhead.

Hope that helps.

[Warboss Alex]

That's how I originally had the boards set up: one table for forum categories, one for forums, one for topics/threads and one for posts. Just wondering if there was a more efficient way to do it, thanks for your advice :D.

I keep reading that two small database tables are better than one bigger one, apparently that doesn't apply here..

Oh, and, say, for the forums index page. Currently I'm iterating through the 'categories' table and then iterating through the forums for each category (i.e. two queries).. is there a more efficient way to do THIS? Perhaps with some DISTINCT or UNION statements?

Thanks for any help :)

[Netizen]

Sounds like you need to do an INNER JOIN between the categories and forums tables based on the category id so you end up with something like

category name, category id, forum name, forum id as the fields returned in each row

[Warboss Alex]

Yes. I hadn't thought of that.. Hum. If I wanted to have the forums in a table, and add a <th> for every category, I'd have do it like this, right?

$cat_name = '';

while ($db->getrow())
{
if ($row['cat_name']!== $cat_name)
{
$cat_name = $row['cat_name'];
print "<th>$cat_name</th>"
}

print "<td>forum stuff</td>";
}

By the way, software like phpbb uses six or seven queries per page on average. How many could I get away with, on a database of my size (100,000 records or so), without it slowing down? Better try it and see I guess..

[Netizen]

Looks right... obviously missing some <tr> tags in there. As for speed/size - that depends on many factors, such as your processor speed, memory, whether you have RAID disks, which version of MySQL (4.0.18 - definitely - has a query cache).

[Warboss Alex]

Server's running 4.0.18-Max, that should be okay then :) ..

Thanks man, you've been very helpful. I've done this sort of thing before, just never having paid too much attention to perfomance (I wanted to get it working first, heh..) ..

[Netizen]

Glad to be of help! I think the query cache is configurable so you need to make sure it is actually turned on :-)