SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Hybrid View

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

    Re-thinking my Primary Key

    I have a "comment" table which is the junction table in a many-to-many relationship between the "member" and "article" tables.

    Here are my tables...

    ARTICLE
    -----------
    - id (pk)
    - slug
    - title


    COMMENT
    -------------
    - member_id (pk)(fk)
    - article_id (pk)(fk)
    - created_on (pk)(fk)


    MEMBER
    ------------
    - id (pk)
    - first_name


    Currently I am adding in the ability for people to "Report a Comment" from a given Article page, which necessitates me passing over the "Article ID", "Member ID", and "Created On Date" to my "report_comment.php" script.

    (For any given Article, the same Member could have posted several Comments, so I need all three pieces of information to know *which* Comment the person is reporting.)

    Simply put, that is a PITA...

    And so i am wondering if this might be a good reason to add an auto-increment "comment.id" field, and make that my Primary Key?!

    Do so would most certainly make my life easier as far as this script I am working on.

    I'm not sure how it might impact other things I might need to do in the future?!

    Currently, I can't think of any places where I am actually using my 3-Key Primary Key other than for ensuring uniqueness in the table...

    Thoughts??

    Thanks,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    And so i am wondering if this might be a good reason to add an auto-increment "comment.id" field, and make that my Primary Key?!
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes
    Still on the "Charge per Word Calling Plan", I see...

    If I create an auto-increment ID for my Primary Key, am I losing anything that the 3 Natural Keys would provide?

    Any "gotchas"?!

    Oh, and is there any easy way to "lay down" auto-numbers for each record, starting at one for the oldest record and cycling through the rest, or is that a programming project?


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Still on the "Charge per Word Calling Plan", I see...
    i like to distill all the yadda yadda yadda down as much as i can

    would you rather i go back to my favourite response... what happened when you tested it???

    don't tell me you're too lazy to fire up a test case and see what happens

    if you do, i'll say i'm too lazy to write you a long-winded free tutorial every time you ask a database question




    Quote Originally Posted by DoubleDee View Post
    If I create an auto-increment ID for my Primary Key, am I losing anything that the 3 Natural Keys would provide?
    if you neglect to declare a UNIQUE key consisting of the same columns you had as the primary key, then yes, and for the same reason you already mentioned -- "ensuring uniqueness in the table"

    Quote Originally Posted by DoubleDee View Post
    Oh, and is there any easy way to "lay down" auto-numbers for each record, starting at one for the oldest record and cycling through the rest, or is that a programming project?
    the easiest way is to create a new table, do an INSERT SELECT from the old table, drop the old table, and rename the new one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i like to distill all the yadda yadda yadda down as much as i can

    would you rather i go back to my favourite response... what happened when you tested it???
    Not really...


    don't tell me you're too lazy to fire up a test case and see what happens
    No, I'm smart and I ask here before I go break things, then once I know I am going down the correct path, then I go off and build and test...


    if you neglect to declare a UNIQUE key consisting of the same columns you had as the primary key, then yes, and for the same reason you already mentioned -- "ensuring uniqueness in the table"
    Does having a "Surrogate Key" (generated) and a "Natural Key(s)" (physical) weigh down your table and queries?

    Seems like doing that whenever possible would be the "best of both worlds", right?


    the easiest way is to create a new table, do an INSERT SELECT from the old table, drop the old table, and rename the new one
    Okay.

    Thanks,



    Debbie

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    How do I make an exact copy of the STRUCTURE of my "comment" table into a new table called "comment"backup"?

    Will this do that...

    Code:
    CREATE TABLE comment_backup LIKE comment;
    (And I mean, same Fields, Data-Types, Attributes, Index, eeverything!)


    Debbie

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Does having a "Surrogate Key" (generated) and a "Natural Key(s)" (physical) weigh down your table and queries?
    well, sort of... but so marginally that you probably couldn't measure the difference, so it's certainly a viable strategy


    Quote Originally Posted by DoubleDee View Post
    Seems like doing that whenever possible would be the "best of both worlds", right?
    correct

    this is actually advocated by many database developers, and i agree with it, but only to the point where the surrogate key has a reason for being, rather than just being rote, having one in every table

    yours was one of the good reasons for having a surrogate pk -- a child table that has to reference this table, but this table has a three-column natural key
    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
  •