[compsci]

Hello all,

I am creating a forum. I am designing the tables for the threads and their replies and the relationship between them. Please have a look at these images and give me your thoughts. I have also included the schema.

http://i284.photobucket.com/albums/l...ableDesign.jpg
http://i284.photobucket.com/albums/l...ogicDesign.jpg

CREATE TABLE  Threads (
       threadID INT NOT NULL AUTO_INCREMENT
     , threadSubject VARCHAR(100) NOT NULL
     , threadContent TEXT NOT NULL
     , link VARCHAR(200)
     , poster VARCHAR(100) NOT NULL
     , PRIMARY KEY (threadID)
);

CREATE TABLE  ThreadReplies (
       replyID INT NOT NULL AUTO_INCREMENT
     , replySubject VARCHAR(100)
     , replyContent TEXT NOT NULL
     , poster VARCHAR(100) NOT NULL
     , PRIMARY KEY (replyID)
);

CREATE TABLE  ThreadsRepliesRelationship (
       threadID INT NOT NULL
     , replyID INT NOT NULL
     , INDEX (threadID)
     , CONSTRAINT ThreadsFK FOREIGN KEY (threadID)
                  REFERENCES  Threads (threadID) ON DELETE CASCADE
     , INDEX (replyID)
     , CONSTRAINT RepliesFK FOREIGN KEY (replyID)
                  REFERENCES  ThreadReplies (replyID) ON DELETE CASCADE
);

1) Is the on cascade correct, i have them in both tables? If a thread reply was deleted would it delete the actual thread - the first post?

Thanks all,

[pootle flump]

Nah. A post is attached to only one thread. And a thread start is only the first post in a thread. So at most two tables, maybe even one.

FYI - you can download loads of free forum packages. Why reinvent the wheel?

[r937]

Originally Posted by compsci

1) Is the on cascade correct, i have them in both tables? If a thread reply was deleted would it delete the actual thread - the first post?

actually, you have your cascades in just one table, the ThreadsRepliesRelationship table

if you delete a row in the Threads table, the corresponding rows in the ThreadsRepliesRelationship would be deleted

if you delete a row in the ThreadReplies table, the corresponding rows in the ThreadsRepliesRelationship would be deleted

i agree that there seems to be at least one table too many here

[r937]

Originally Posted by pootle flump

FYI - you can download loads of free forum packages. Why reinvent the wheel?

i can answer that one...

- because free forum packages are overly complex
- because free forum packages have huge security holes
- because free forum packages don't do what i need
- because free forum packages take too long to understand
- because free forum packages use technology my host doesn't support
- because free forum packages require a different url scheme
- because free forum packages cannot integrate with the rest of my site
- because free forum packages suck the big one111!!1!1

helps?

[compsci]

Originally Posted by r937

actually, you have your cascades in just one table, the ThreadsRepliesRelationship table

if you delete a row in the Threads table, the corresponding rows in the ThreadsRepliesRelationship would be deleted

if you delete a row in the ThreadReplies table, the corresponding rows in the ThreadsRepliesRelationship would be deleted

i agree that there seems to be at least one table too many here

That seems fine to me so far, but how can i add one more cascade so that when a row in the Threads Table is deleted all corresponding rows in the ThreadReplies is deleted?

Is there a better way than the approach i have taken so far?

[compsci]

lol i was going to reply with only a few reaons, but all mine are in r937's post!

The main reason for me is that they are not that customizable - i want a really light weight forum. A really really simple one. Plus i like doing it myself so that if something does go wrong i know where to look.

[r937]

Originally Posted by compsci

That seems fine to me so far, but how can i add one more cascade so that when a row in the Threads Table is deleted all corresponding rows in the ThreadReplies is deleted?

you cannot

[compsci]

Originally Posted by r937

you cannot

Do i have to remove the table ThreadsRepliesRelationship for that to work?

Is that a good idea or is there a better approach to this threads table design?

[r937]

yes, i would remove it, as poots suggested

the reason is, a reply typically belongs to only one thread

add threadID to the ThreadReplies table and drop the ThreadsRepliesRelationship table

[compsci]

Oh lol - I didn't even think of that! Thanks guys!

[pootle flump]

Originally Posted by r937

helps?

No. I know packages that don't tick most if not all of those boxes. The code is totally configurable. You can get forums written in pretty well any language under GPL. Different URL scheme - you not got a text editor? How can it take longer to use a WYSIWYG forum tool than to build one from scratch? Add loads of MODs, cummunity support, 10s to 10000s of reference sites.

If you want to do one to learn or for the sake of it then fair enough. But I think that of almost all the packages you might build yourself vs get for free I reckon building a forum represents one of the worst ROIs on your time there is.

So - NAH!

[r937]

Originally Posted by pootle flump

No. ... [snip a bunch of crap] ... So - NAH!

url(s) of the forum(s) you implemented, please

[pootle flump]

All internal - there will be loads eventually but all closed to riff raff. I've been evaluating packages (snitz, phpBB, Yet Another Forum). Thread recently posted in the mod forum

[pootle flump]

Oh - beg your pardon - links to forums:
www.snitz.com
www.yetanotherforum.net
www.phpbb.com

[r937]

hey, that's great news, poots, i may need to talk to you about your experiences for a project i'm working on

no vBulletin?