SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Designing tables for Comments to Articles

    I want to allow registered users to be able to add comments to articles on my website. In addition, registered users can "vote" on other people's comments, however they can only vote once per other comment.

    It would be easier to display an ERD, but here is what I have in text terms...

    - One and only one ARTICLE can have zero to many COMMENT

    - One and only one MEMBER can have zero to many COMMENT

    - One and only one COMMENT can have zero to many VOTE

    - One and only one MEMBER can have one and only one VOTE on any given COMMENT
    I believe this design is correct, however, I am totally unsure of of how to model things so that a MEMBER can only VOTE once on any given COMMENT?!

    Thanks,



    Debbie

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Create a UNIQUE constraint on (member_id, comment_id) in the VOTE table. A second row with the same pair can't be inserted.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Create a UNIQUE constraint on ...
    aw, heck, just make that the primary key

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    aw, heck, just make that the primary key

    I don't follow you?!

    The PK of any table is always "unique".

    But ensuring that CommentID + MemberID is unique would appear to ensure that a Member doesn't vote multiple times on the same Comment.


    Debbie

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    But ensuring that CommentID + MemberID is unique would appear to ensure that a Member doesn't vote multiple times on the same Comment.
    bingo, you gots the idea

    so make CommentID + MemberID the PK

    PKs are, as you know, unique by definition

    you don't need anything else

    or did you, perhaps, think that there was gonna be an auto_increment PK for this table? because that would be wasteful and unnecessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    bingo, you gots the idea

    so make CommentID + MemberID the PK

    PKs are, as you know, unique by definition

    you don't need anything else

    or did you, perhaps, think that there was gonna be an auto_increment PK for this table? because that would be wasteful and unnecessary
    I guess I was always taught to create an "ID" for ever table that was auto increment.

    So you're saying don't do that?


    Debbie

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So you're saying don't do that?
    yes, don't do that

    create an auto_increment id as the primary key only if the table does not have a suitable natural key

    in the case of a relationship table, the two foreign key columns taken together are a perfect primary key, and having an auto_increment id is counter-productive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    aw, heck, just make that the primary key

    But I just realized there is a problem...

    The COMMENT table has a PK made up of article_id and member_id.

    The MEMBER table has a pk made up of member_id.

    So how can I have a composite key for the VOTE table as it would be...

    Code SQL:
    PRIMARY KEY(article_id, member_id, member_id)




    Debbie

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i guess you haven't actually created these tables yet?

    because if a comment belongs to an article and is also associated with a member (the one who made the comment), then the PK of the comment table would be article_id plus member_id

    so if other members can vote on the comment, therefore you need a second member_id in the vote table

    so the PK of the vote table would be article_id plus member_id (who wrote the comment) plus member_id (who made the vote)

    so your vote table would have two member_id columns, and you wouldn't of course be able to give both of these columns the name "member_id"

    in a similar way, in a database which keeps track of sports games, the games table has two team_id columns, one is often called home_team_id and the other away_team_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i guess you haven't actually created these tables yet?
    Yes, I did last night, but wasn't getting it.


    because if a comment belongs to an article and is also associated with a member (the one who made the comment), then the PK of the comment table would be article_id plus member_id
    Right.


    so if other members can vote on the comment, therefore you need a second member_id in the vote table

    so the PK of the vote table would be article_id plus member_id (who wrote the comment) plus member_id (who made the vote)

    so your vote table would have two member_id columns, and you wouldn't of course be able to give both of these columns the name "member_id"

    in a similar way, in a database which keeps track of sports games, the games table has two team_id columns, one is often called home_team_id and the other away_team_id
    Okay, I wasn't seeing that!

    Here is a dump of my tables...

    article
    Field Type Null Default Comments
    id mediumint(8) No
    title varchar(250) No
    subtitle varchar(250) Yes NULL
    article_date date No
    author varchar(100) Yes NULL
    body text No
    end_notes text Yes NULL
    created_on datetime No
    updated_on datetime Yes NULL


    comment
    Field Type Null Default Comments
    article_id mediumint(8) No
    member_id mediumint(8) No
    body text No
    status varchar(20) No
    created_on datetime No
    approved_on datetime Yes NULL
    updated_on datetime Yes NULL


    member
    Field Type Null Default Comments
    id mediumint(8) No
    email varchar(40) No
    pass char(40) No
    first_name varchar(20) No


    vote
    Field Type Null Default Comments
    article_id mediumint(8) No
    member_id mediumint(8) No
    voting_member_id mediumint(8) No
    score tinyint(4) No
    created_on datetime No
    updated_on datetime Yes NULL

    ----------
    Indexes:
    Keyname Type Cardinality Field
    PRIMARY PRIMARY 0 article_id, member_id, voting_member_id


    How does that look?


    Debbie

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    How does that look?
    not bad at all

    i personally would not use member_id for one of the columns, i would choose a role-based name for both of them, as in member_commenting and member_voting (similar to home_team and away_team for the team_ids in a game table)

    as for showing the table structure, it's far better to copy the results of a SHOW CREATE TABLE statement rather than copying/pasting from a phpmyadmin display which is what it looks like you did -- SHOW CREATE TABLE is much more comprehensive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not bad at all
    Good.


    i personally would not use member_id for one of the columns, i would choose a role-based name for both of them, as in member_commenting and member_voting (similar to home_team and away_team for the team_ids in a game table)
    Good idea.


    as for showing the table structure, it's far better to copy the results of a SHOW CREATE TABLE statement rather than copying/pasting from a phpmyadmin display which is what it looks like you did -- SHOW CREATE TABLE is much more comprehensive
    Okay.

    Thanks,


    Debbie

  13. #13
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I just realized another small problem...

    How do I allow a MEMBER to make multiple COMMENTs for a given ARTICLE?

    If the pk for COMMENT is (article_id, member_id) then there could only be one comment.

    Should I make the pk for COMMENT a new comment_id?

    Or could I append the create_date to form a new pk of (article_id, member_id, create_date)?


    Debbie

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Or could I append the create_date to form a new pk of (article_id, member_id, create_date)?
    since you have created_on in the table anyway, adding it to the PK isn't like adding an additional table column, it's a freebie, and it solves the PK issue nicely

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Such a complicated PK is hard to pass around to do things like moderate, delete, reply to comments, though. Every URL/form would need to include an article ID, member ID and a timestamp. An auto increment column would make those things easier and make the votes table smaller.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    An auto increment column would make those things easier ...
    you're absolutely right

    Quote Originally Posted by Dan Grossman View Post
    ... and make the votes table smaller.
    you're absolutely wrong, it'll be bigger

    what, you're proposing that the comment_id, member_id, and created_datetime be dropped from the table?

    tee hee
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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