Forum structure?

Parvenu

I'm going to set about building a PHP/MySQL forum shortly, with separate forums regarding separate topics, much like this one.

Could anyone point me in the right direction as regards database structure? My original idea was to create a new table for each thread to contain the original message and replies, but I read somewhere that a good method is to use one big table to contain messages and replies and simply link posts together using ID's.

Which of these methods is better (or is there a better one somehwere I haven't thought of)?

Help will be much appreciated, I'm new to these fabulous new technologies.

tonyw

you need

a table of users
a table of forums - eg database, php, mysql, etc
a table of messages

each message has a parent message that it is a reply to. A parent of zero means a new topic message. Do this and you have the option to display them threaded correctly if you wish.

each message also belongs to a particular forum.

sort em by date and you have a forum 🙂

forums are great little projects - mine is here http://www.georgestobbart.com/ Keeps me off the streets

plaggypig

Hey up,

One table for every thread would be terribly inefficient and inpractical. All you need are 2 tables, for example:

threads_tbl

thread_id - Mediumint(5) NOT NULL AUTO_INCREMENT UNIQUE
date_modified - Datetime NOT NULL
topic - Tinytext NOT NULL
author - Tinytext NOT NULL
Primary Key (thread_id)

posts_tbl

post_id - Mediumint(5) NOT NULL AUTO_INCREMENT UNIQUE
thread_id - Mediumint(5) NOT NULL
date_posted - Datetime NOT NULL
author - Tinytext NOT NULL
body - Text NOT NULL
Primary Key (post_id)

For every thread in your message board you must make 1 row in 'threads_tbl', and initially 1 row in 'posts_tbl'. Whenever a reply is made to a thread you add a new row into 'posts_tbl', and update the 'date_modified' field to the current timestamp for the corresponding row in 'threads_tbl'.

I actually use a 'user_id' field in both tables rather than 'author', which links to my master 'users_tbl' containing user info, such as username.

Hope this gives you some ideas.

Thanks, Andy.