Need help w/ database

[The Hobo]

I'm working on creating a Forum program in PHP, and I'm trying to figure out how to setup the database. Should I have:

Two Tables: threads and posts (replies). Threads would contain the original (1st post) of the forum, and posts have all the replies. Like:

threads: id, title, ...
posts: id, title, ..., threadID

One Table: one table for threads and posts (replies). All are stored in one thread. But how to tell which is a thread starter and which is a reply, then how to tell which reply goes with which thread

Many Tables: 2 tables for each forum. like forum1 and forum1r, forum2 and forum2r. So like "General VB" would be forum1 and the replies would go in forum1r. "Chit Chat" would be forum2 and the replies would go in forum2r.

Or...what? I'm open to ideas on this as I have no idea how to setup the databases.

[filburt1]

I made a bug reporting system where the first "post" (the report itself) is in one table and all followups (replies) are in another. It's very easy that way, but vBulletin doesn't work like that.

A table for each forum would be an incredible mess and could mean more queries.

[The Hobo]

That's how I'm doing it now. With the two tables, threads and replies. Each new thread (the title, body, who created it, etc) are stored in threads, and each reply is stored in replies (all the same info). In replies, there's a field for "threadID" which stores what thread the reply belongs to.

I was just wondering if anyone had any better ideas.

[filburt1]

As a side note, could you use a database structure dump for vBulletin just for reference?

[The Hobo]

You mean steal their database structure? I kinda wanna figure this out on my own.

And on a side note, I plan on stealing your idea for the quote tag.

[Xmevs]

I suggest you use one table for the whole thing, and use a field: "first enum('0','1') not null default 0" to separate the first posts from the other ones, and use another field: "parent int(n)" for the other posts (with the first post, this can be null). You'll save space that way.

[Kagey]

Or you could have 1 table with post_id, parent_id. Set the parent_id to 0 if it is a top level message and set parent_id to the post_id of the top level message if it is a reply. That way you dont have 2 tables that are pretty much identical. it makes it easier to manage in the future.

then in your sql to show threads in a forum, reference the table twice, one reference would do the messages with parent_id = 0, and the other would count the amount of messages with parent_id equal to the post_id of the first reference.

dont know if that makes sense

[The Hobo]

Yeah, it makes sense.

I'm still liking the two table idea though. What would be the draw backs to this, besides having two almost identical tables?

[Kagey]

The lurking, irritating idea that it could of been one table...

[The Hobo]

Originally posted by Kagey
The lurking, irritating idea that it could of been one table...

Yes, but while I would only have one table, I would also have twice as many records in that table as well. I find the two table idea much more appealing.

And it is much better than what I mentioned, having 2 tables for each forum