SitePoint Sponsor

User Tag List

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

    Impact of Late-Game Changes to Tables?

    So I am wrapping up v2.0 of my website, and being the perfectionist that I am, I would like to tweak MySQL, but am unsure of the consequences of this?!

    (BTW, the purpose of these questions is to identify any *obvious* issues that I might create. Since you guys can't see my entire code base, I realize my questions below are somewhat vague...)


    Proposed Change 1:
    I have some Junction Tables which have Composite PK's, and I would like to add an "id" field that is an AutoIncrement and a UK.

    This shouldn't affect any table relationship.

    And it shouldn't impact any SELECT statements.

    Could it impact any INSERT statements that I have in my PHP code? (Or will MySQL take care of the AutoIncrement itself?)


    Proposed Change 2:
    I have the following table...

    article_thread
    Code:
    id (PK)
    article_id (U1)(FK)
    member_id (U2)(FK)
    subscribed_on (U3)
    I would like to flip the PK and UK's.

    Will MySQL or any SELECT, UPDATE or INSERT statements, or my PHP care?

    As far as I can see, this should only impact my Foreign Key Constraints....

    Sincerely,



    Debbie

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Proposed Change 1:
    I have some Junction Tables which have Composite PK's, and I would like to add an "id" field that is an AutoIncrement and a UK.

    This shouldn't affect any table relationship.

    And it shouldn't impact any SELECT statements.

    Could it impact any INSERT statements that I have in my PHP code? (Or will MySQL take care of the AutoIncrement itself?)
    Provided that you either used named inserts (INSERT INTO blah SET a=1, b=2) or VALUES INSERTS naming all values (INSERT INTO blah (a, b) VALUES (1,2)) then you shouldn't have any problem. If you've used INSERT INTO blah VALUES(1), omitting a value for b MySQL will complain that you didn't supply all necessary fields.

    Bigger question is, why would you want to do this? Why add a field that adds no information, doesn't help in your code, and is basically just wasting space?

    Quote Originally Posted by DoubleDee View Post
    Proposed Change 2:
    I have the following table...

    article_thread
    Code:
    id (PK)
    article_id (U1)(FK)
    member_id (U2)(FK)
    subscribed_on (U3)
    I would like to flip the PK and UK's.

    Will MySQL or any SELECT, UPDATE or INSERT statements, or my PHP care?
    What is the subscribed_on field? Where will it map in terms of FK?
    At first sight I'd say you can change it without any problems, but it depends on your answer on the question above.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  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 ScallioXTX View Post
    Provided that you either used named inserts (INSERT INTO blah SET a=1, b=2) or VALUES INSERTS naming all values (INSERT INTO blah (a, b) VALUES (1,2)) then you shouldn't have any problem. If you've used INSERT INTO blah VALUES(1), omitting a value for b MySQL will complain that you didn't supply all necessary fields.
    If I am understanding you, then a query like this should not be negatively impacted, right?
    PHP Code:
        // Build query.
        
    $q3 "INSERT INTO comment(article_id, member_id, comment_no, body, created_on)
                VALUES(?, ?, ?, ?, NOW())"


    Quote Originally Posted by ScallioXTX View Post
    Bigger question is, why would you want to do this? Why add a field that adds no information, doesn't help in your code, and is basically just wasting space?
    Some of my tables have 3 and 4 Composite Keys, and it is a real PITA to have to reference a particular record when this is all you have...
    Code:
    article_id	question_id	member_id
    1		1		19
    1		1		20
    1		1		51

    In the case of a Junction Table like that, this would be much more practical...
    Code:
    id	article_id	question_id	member_id
    25	1		1		19
    26	1		1		20
    27	1		1		51
    The second example allows me to...

    1.) Visually spot a record with ease
    2.) Helps to clearly define the exact order in which records were entered
    3.) If I ever needed to use this table as a parent table, then it would be much easier to do a join using the "id" field versus the other three fields
    4.) It gives me another way to sort records to find what I need

    So there are 4 reasons right there off the top of my head...

    And having one extra "integer" field in a table adds virtually no extra space.



    Quote Originally Posted by ScallioXTX View Post
    What is the subscribed_on field? Where will it map in terms of FK?
    At first sight I'd say you can change it without any problems, but it depends on your answer on the question above.
    It is a Date-Time field of when the person subscribed.

    Sincerely,


    Debbie

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    If I am understanding you, then a query like this should not be negatively impacted, right?
    PHP Code:
        // Build query.
        
    $q3 "INSERT INTO comment(article_id, member_id, comment_no, body, created_on)
                VALUES(?, ?, ?, ?, NOW())"

    Correct

    Quote Originally Posted by DoubleDee View Post
    Some of my tables have 3 and 4 Composite Keys, and it is a real PITA to have to reference a particular record when this is all you have...
    Code:
    article_id	question_id	member_id
    1		1		19
    1		1		20
    1		1		51

    In the case of a Junction Table like that, this would be much more practical...
    Code:
    id	article_id	question_id	member_id
    25	1		1		19
    26	1		1		20
    27	1		1		51
    The second example allows me to...

    1.) Visually spot a record with ease
    2.) Helps to clearly define the exact order in which records were entered
    3.) If I ever needed to use this table as a parent table, then it would be much easier to do a join using the "id" field versus the other three fields
    4.) It gives me another way to sort records to find what I need

    So there are 4 reasons right there off the top of my head...

    And having one extra "integer" field in a table adds virtually no extra space.
    I'm thinking a question belongs to an article? If so, you don't need to store both in a junction table. The way you have it set up now is that multiple different members can ask the same question to multiple different articles. If that's the case then go for it. Otherwise, I'd change the structure.

    Quote Originally Posted by DoubleDee View Post
    It is a Date-Time field of when the person subscribed.
    Why is it in this table? Why not join on your member table instead, I suppose that's where you copying the value from?

    As far as swapping FK and UK, it shouldn't have any impact, but making subscribed_on an FK doesn't make sense, since it's not the PK of any other table. Suppose you make it an ON DELETE CASCADE foreign key, and you have two members who signed up at exactly the same time, and you delete one of them, you delete the article_thread entries from the other one as well.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  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 ScallioXTX View Post
    Quote Originally Posted by DoubleDee
    Some of my tables have 3 and 4 Composite Keys, and it is a real PITA to have to reference a particular record when this is all you have...
    Code:
    article_id	question_id	member_id
    1		1		19
    1		1		20
    1		1		51
    In the case of a Junction Table like that, this would be much more practical...
    Code:
    id	article_id	question_id	member_id
    25	1		1		19
    26	1		1		20
    27	1		1		51
    The second example allows me to...

    1.) Visually spot a record with ease
    2.) Helps to clearly define the exact order in which records were entered
    3.) If I ever needed to use this table as a parent table, then it would be much easier to do a join using the "id" field versus the other three fields
    4.) It gives me another way to sort records to find what I need

    So there are 4 reasons right there off the top of my head...

    And having one extra "integer" field in a table adds virtually no extra space.
    I'm thinking a question belongs to an article? If so, you don't need to store both in a junction table. The way you have it set up now is that multiple different members can ask the same question to multiple different articles. If that's the case then go for it. Otherwise, I'd change the structure.
    No. I have an ARTICLE table and a SURVEY_QUESTION table which are joined by a SURVEY_MAP table which defines which Questions are asked for which Articles.

    Then I have a SURVEY_RESULTS table which combines the entities ARTICLE, SURVEY_QUESTION, and MEMBER. So things are modeled correctly.

    And as mentioned above, it is *always* easier to look for Record ID = 5823 than to have to scroll down a table looking for a combination of 3 to 4 keys as shown above.


    Quote Originally Posted by ScallioXTX View Post
    Quote Originally Posted by DoubleDee
    It is a Date-Time field of when the person subscribed.
    Why is it in this table? Why not join on your member table instead, I suppose that's where you copying the value from?
    Because an ARTICLE_THREAD is the combination of an ARTICLE, a MEMBER, and a DATETIME.

    article_thread
    Code:
    article_id (PK1)(FK)
    member_id (PK2)(FK)
    subscribed_on (PK3)
    (BTW, if it isn't clear, I have "subscribed_on" as part of the PK because you can "subscribe" and "unsubscribe" as much as you like.


    Quote Originally Posted by ScallioXTX View Post
    As far as swapping FK and UK, it shouldn't have any impact, but making subscribed_on an FK doesn't make sense, since it's not the PK of any other table. Suppose you make it an ON DELETE CASCADE foreign key, and you have two members who signed up at exactly the same time, and you delete one of them, you delete the article_thread entries from the other one as well.
    See above.

    Sincerely,


    Debbie

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    No. I have an ARTICLE table and a SURVEY_QUESTION table which are joined by a SURVEY_MAP table which defines which Questions are asked for which Articles.

    Then I have a SURVEY_RESULTS table which combines the entities ARTICLE, SURVEY_QUESTION, and MEMBER. So things are modeled correctly.

    And as mentioned above, it is *always* easier to look for Record ID = 5823 than to have to scroll down a table looking for a combination of 3 to 4 keys as shown above.
    Okay, yes, then your setup is correct.

    Quote Originally Posted by DoubleDee View Post
    Because an ARTICLE_THREAD is the combination of an ARTICLE, a MEMBER, and a DATETIME.

    article_thread
    Code:
    article_id (PK1)(FK)
    member_id (PK2)(FK)
    subscribed_on (PK3)
    Okay, then yes you can make the change, but make sure to make one FK only for (member_id, subscribed_on), not two separate FKs, i.e.:

    Code:
    id (PK)
    article_id (FK1) (U1)
    member_id (FK2) (U1)
    subscribed_on (FK2)
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    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 ScallioXTX View Post
    Quote Originally Posted by DoubleDee
    Because an ARTICLE_THREAD is the combination of an ARTICLE, a MEMBER, and a DATETIME.

    article_thread
    Code:
    article_id (PK1)(FK)
    member_id (PK2)(FK)
    subscribed_on (PK3)
    Okay, then yes you can make the change, but make sure to make one FK only for (member_id, subscribed_on), not two separate FKs, i.e.:

    Code:
    id (PK)
    article_id (FK1) (U1)
    member_id (FK2) (U1)
    subscribed_on (FK2)

    You are mixing up different concepts...


    Concept 1:
    In the ARTICLE_THREAD table, I need 3 fields to make a Composite PK: article_id, member_id, subscribe_on


    Concept 2:
    To ensure Referential Integrity, I have this...
    Code:
    ARTICLE.id -||-----|<- ARTICLE_THREAD.article_id
    
    MEMBER.id -||-----|<- ARTICLE_THREAD.member_id
    I am linking two Parent Tables to the Junction Table, so there are only TWO Foreign Keys... ("subscribed_on" provides no linking.)

    Sincerely,


    Debbie


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
  •