Database Structure for a Forum Script

[nekoder]

Hi,

I need some suggestions and the brains of PHP enthusiasts! I'm planning to implement a forum from scratch but I'm having a hard time figuring out what it needs for the database structure. I'll like to have the admin authorize the message before it is actually displayed on the user pages.

I have come up with a few columns that may be required such as:

MESG_ID
MESG_BODY
MESG_SUBJECT
POST_DATE
AUTHOR
APROVE

But from this point on, I'm stuck. How do you handle the thread part? What other fields do I need to include? If there are some forums in php using mysql, please let me know. So far I've seen some and I don't know how the table structure is setup to handle a forum.

Thanks in advance.

[Son Nguyen]

Why don't you get the idea of other forums? Download vB Lite, BoardPower, Phorum,... for examples.

[Skunk]

If you are going for a simple layout similar to vBulletin (but without the polls, categories etc) you will need 3 main tables:

Forums
=====
forum_id
forum_name

Threads
======
thread_id
thread_name
thread_forum <-- the ID of the forum the thread appears on

Posts
====
post_id
post_text
post_author
post_thread <-- the ID of the thread the post appears on
post_date

For each thread simply list all of the posts that are on that thread (identified by the post_thread field) and order them by date.

Obviously you'll want to make things a bit more complicated than that - you'll probably want another table for forum users etc - but that shows you the basic theory behind a database design for a forum.

I wouldn't recommend downloading vBulletin 2.0 and checking out its database structure unless you are quite experienced with database design techniques - vBulletin 2.0 actually uses some quite complex techniques (for speed optimisation reasons) that could cause confusion if you aren't sure what they are for.

[Skunk]

Oops - one thing I forgot.

If you want admins to have to approve post before they appear you will need another field in the basic posts table:

Posts
====
post_id
post_text
post_author
post_thread <-- the ID of the thread the post appears on
post_date
post_validated <-- defaults to no

When a use adds a new post the post_validated field is set to no. Only admins (using an admin tool) can set this field to 'yes'. The thread display script will only display posts that have post_validated = yes (you can do this in the SQL query).

[kunal]

A good way to make a MySQL table layout, would be to decide what options or features you wanna provide. Also, you should think big. Make sure everything is open to change. Becuase stoping a feature is easy, but adding a new one can be a head ache.

Break your tables up into smaller peices.
For example
1. should store only user info.
2. should store only admin ingo.
3. should store only category info.

etc etc....

The main thing you should keep in mind is, think [size=5]BIG[/size=5]