Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Unanswered: Database Design For Forum

    Here's a design I was thinking for a forum I'm making right now. Do you think this design will work efficiently. Should I create more posts tables to allow faster querying, or is one fine?

    Forums:
    id(int)(primary), name(varchar)(unique), createdeleteboard (int-refers to some security table issuing moderator officer status etc.)

    Boards:
    id(int)(primary), forumid(int)(index), name(varchar)(unique), desc(varchar)(unique), created(bigint-seconds since epoch), viewsecurity(int), deletethreadsecurity(int), createthreadsecurity(int), stickythreadsecurity(int)

    Threads:
    id (int)(primary), boardid(int)(index), title(varchar)(unique), authorid(int)(index), created(bigint), views(int), stickied(int)(0 or 1 value), viewsecurity(int), replysecurity(int), deletepostsecurity(int)

    Posts:
    id(int), forumid(int)(index), text(mediumtext), authorid(int)(index), created(bigint)

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Why bother? There are plenty of ready made solutions available for much less than it will cost you to re-invent the wheel.

    For whatever it's worth, your schema doesn't appear to allow posts in reply to other posts (just like this forum!). For lengthy threads I think multi-threaded discussions are more effective.

  3. #3
    Join Date
    Nov 2009
    Posts
    5
    Also I would like to use transactions so i was thinking InnoDB. But for searching purposes shouldn't the text column have a fulltext index which requires myisam. Which do you think I should use?

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    Well I just want to have my forums integrate with the site a bit, plus I like a challenge. I'm not sure if I need a reply bit, just because all I need is a basic forum. Also I have a pm system set up, so if someone really wants to reply to specific posts they can just message the user. Also this is a bit slim, like I plan on adding some other stuff to posts such as embedded images.

  5. #5
    Join Date
    Nov 2009
    Posts
    5
    Also if you just quote a posts statements, that gives the impression of a reply.

  6. #6
    Join Date
    Nov 2009
    Posts
    5
    Saw a typo...forumid under posts should be threadid.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not sure i understand the difference between forums and boards

    i might combine threads and posts (i.e. the first post becomes the thread), but you can also have them separate, as many other people do

    one minor point: consider datetime instead of epoch integers
    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
  •