Forums Database Structure

I've been thinking about creating basic forums attached with my website ever since I started it, and I think i'm finally going to start the project. I have a basic idea of how I want to set them up, but my main concern is the database structure.

I could have it create a seperate table for each sub-forum, or I could have one huge table with an ID column to fetch the posts from that table. Either would work...but what would you suggest?

You should visit hotscripts.com or aspin.com and download a few of the existing forum apps, and look over the db structures used. Most are pretty similar.

I would go with more tables, less data in each. basic structure I would go with:
1. Users: ID, Name, ...
2. Forums: ID, Name, ... (more details like forum description)
3. Threads: ID, Name (title of thread), Forum, ... (no post data!)
4. Posts: ID, User, Thread, Contents, TimePosted, ...

• all ID fields will be auto number and PK.
• thread starter is the User who posted the first Post in this Thread. same for the
  thread time - the TimePosted of its first Post.
• have minimal amount of varchar fields, use as much numeric fields as possible.
• for permissions, look in the thread started by JMH in the Lounge, you can store
  lots of permissions using single number.
• I would prefer to avoid storing all the posts in single table - it would make the
  table huge, slowing down search time. when storing post, check the size: if it's
  less than kilo byte, for example, store it in the table itself. otherwise, store it
  inside text file and put full path of the text file in the Posts table. The downside
  is you won't be able to search within posts - one solution is creating daily Index
  of some sort.

I believe most modern forums use at least part of the above logic, so you can
look in existing forums as Doug suggested instead of re-inventing the wheel.

As doug said i would download a couple of free ones that work well and see how their databases are structured. phpBB is free and i have somewhere the sql file of IPB before it was charged for if you want that it could help you. Just PM me if you want it.

RF

Thanks for your inputs guys. It's all much appreciated.

I'm goin to bed (next post i make is 1500). Not much these days, but considering i only drop by to help people out whenever i have free time, that's good