[eluser]boltsabre[/eluser]
Hi guys, need some advice from those of you with some experience in large websites.
I'm building a website which is going to have about different 20 forums on it. In the initial stages it was only 5, and I designed it with 5 different (sets of) tables (posts, replies, subscribers, logs) - one set for each forum. However, the project scope has changed and it's now in the order of 20+.
My initial justification for using a different set of tables for each forum was that the increased performance with the sql queries would offset the upkeep of maintaining 5 tables (ie, if later there needed to be an extra column per forum, or to change a datatype, etc). But with 20+ forums, a change in anything DB/table related would require substantial effort, and a large increase in potential bugs, data corruption, etc.
What's your thoughts on the matter? Have one table per forum so as to reduce the number of where statements in the sql, or combine all 20 forums into one super table?
Code:
// one forum per table
// simple quick sql to get 20 forum posts per page, the only where clause is if post is deleted or not and report_count (3 user reports and it's not displayed until admin has approved/edited it).
SELECT
f.primary_key, f.title, f.post_date, f.reply_count, u.username
FROM
forum1_posts AS f
INNER JOIN
users AS u
ON
f.user_id = u.user_id
WHERE
f.deleted = 0 AND
f.report_count < 3
LIMIT 20 OFFSET (pagination offset).
Code:
// super forum table with an extra column (forum_type_id), an extra index,
// increased where clauses and 20 times the amount of data in the table
SELECT
f.primary_key, f.title, f.post_date, f.reply_count, u.username
FROM
forum1_posts AS f
INNER JOIN
users AS u
ON
f.user_id = u.user_id
WHERE
f.forum_type_id = 1 AND
f.report_count < 3 AND
f.deleted = 0
LIMIT 20 OFFSET (pagination offset).
As you can see, going with a super table means there will be much more data, an extra column with an extra where clause, an extra index, and other such things.
What's your thoughts on the best way to proceed?
- It's a mysql database
- Shared website hosting in the initial stages. If it's profitable they will look at dedicated or virtual hosting (although I've been told that the shared hosting plan actually has separate, but still shared, dedicated mysql/db servers and isn't as bad as something like hostgators shared hosting plans, from what I understand it's a high end, but yes, still shared, hosting plan).
