I am in the process of rebuilding my forums running on PHP and mySQL. It's not my first forum system that I am building, but I am hoping for some suggestions for the database structure (layout).
The forums will be for a pretty large website. Right now, the forums I have aren't layed out optimally for the 55,000 posts they contain. I have tried coming up with some new structure ideas for the database, but after recoding for the second time, I am not happy with the results. So I have come to you for input.
The current running system is pretty straight forward.
Forums
- Categories
- - Topics
- - - Posts
- - - - Threads (an extension of posts containing the text to lighten the load on Posts)
The problem I see is that the queries have to sift through the 55,000 posts and threads each time.
I thought it might be better this time to divide up the work by creating a separate database table for each of the categories containing
Topics
- - Posts
- - - Threads
After almost completing the job (second time around), I feel that I have too many tables to run around to get all the features I want. For instance, what if I want to list all the posts made by a specific user? I can't just query a single table, I have to query all the Posts tables, then sort the results by date, then display them. This sounds like MUCH more work then before. Creating a separate table just for the users and their posts info, then I will again have a table with 55,000 rows of data.
Perhaps the tables are not at fault and it's my code? What kind of limits do you think I can impose on the system?
You should also know that the forums are not the only thing using the database... the website is a CMS which means every page and section uses the database as well. The forums, at this point, are the causing the most strain on the server ... so here is where I start.
I look forward to any suggestions you may have.
