Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Forum Threads Table Design

    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

    Code:
    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,

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote 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?

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote 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?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Oh lol - I didn't even think of that! Thanks guys!

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote 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!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    No. ... [snip a bunch of crap] ... So - NAH!
    url(s) of the forum(s) you implemented, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh - beg your pardon - links to forums:
    www.snitz.com
    www.yetanotherforum.net
    www.phpbb.com
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •