SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    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,263
    Mentioned
    60 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,777
    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,263
    Mentioned
    60 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,777
    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,777
    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,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Will this do that...
    i was gonna try and wait 24 hours before replying, but i didn't want to let someone else jump in and say this ....

    what happened when you tested it?



    srsly, i can't believe you set that up so easily
    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,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i was gonna try and wait 24 hours before replying, but i didn't want to let someone else jump in and say this ....

    what happened when you tested it?

    It looks like it worked, but I am unsure of myself...

    Again, I'm looking for a little "You are going about this the right way." (Or, "That will blow up your database?!")


    srsly, i can't believe you set that up so easily
    If everything was easy, I wouldn't come here for help...


    Debbie

    P.S. r937, you might want to factor in that my code-base is probably over 10,000 lines of code now, and of that, I have slaved and written at least 8,000 lines of that code entirely myself. The other 2,000 was from nice, generous, and smarter-than-Debbie people like you here on SitePoint. Try cutting me some slack...

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 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"

  10. #10
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    well, sort of... but so marginally that you probably couldn't measure the difference, so it's certainly a viable strategy
    So until I get a database with 1,000,000 Users in it, I am probably good, right?!


    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
    And what would be some "rote" examples?

    Here are some design decisions I made long ago (but they might need to be tweaked)...

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


    MEMBER
    -------------
    - id (pk)
    - email (uk)
    - username (uk)


    PRIVATE MESSAGE
    -----------------------
    - id (pk) **nothing to physically make certain every PM is unique, but I don't think you want to do that?!


    And of course...

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


    COMMENT (revised)
    -------------
    - id (pk)
    - article_id (uk)
    - member_id (uk)
    - created_on (uk)


    Thoughts on these?

    Thanks,


    Debbie

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Thoughts on these?
    nope

    you've exhausted your three free tutorials for today, sorry

    someone else, i am sure, will be happy to pick up the torch

    here they come now...
    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
  •