1 2 3 Previous Next 38 Replies Latest reply: Sep 24, 2013 5:52 PM by jgarry Go to original post RSS
      • 30. Re: Determining Keys for Junction Table
        Hemant K Chitale

        >I have been slammed pretty good in this thread that I am breaking all of these rules and throwing "best practices" out the window

        No, we never said that you cannot have a Unique Constraint (UK) (on a different column or set of columns) in addition to a Primary Key (PK).  What we were pointing out was that we disagreed with your definitions of PK and FK.

         

        Hemant K Chitale

        • 31. Re: Determining Keys for Junction Table
          Debbie_1004338a

          HemantKChitale wrote:

           

          >I have been slammed pretty good in this thread that I am breaking all of these rules and throwing "best practices" out the window

          No, we never said that you cannot have a Unique Constraint (UK) (on a different column or set of columns) in addition to a Primary Key (PK).  What we were pointing out was that we disagreed with your definitions of PK and FK.

           

          I'd still be curious to see what rp0428 has to say to my Post #26...

           

           

          Debbie

          • 32. Re: Determining Keys for Junction Table
            Debbie_1004338a

            HemantKChitale wrote:

             

            >I have been slammed pretty good in this thread that I am breaking all of these rules and throwing "best practices" out the window

            No, we never said that you cannot have a Unique Constraint (UK) (on a different column or set of columns) in addition to a Primary Key (PK).  What we were pointing out was that we disagreed with your definitions of PK and FK.

             

            Hemant K Chitale

             

            While waiting on rp0428, can you please comment on this latest design...

             

            MEMBER

            - id (PK)

            - email (UK)

            - username (UK)

            - name

            - password

             

            ARTICLE

            - id (PK)

            - slug (UK)

            - heading

            - body

             

            ARTICLE_THREAD (aka "Subscription)

            - id (PK)

            - article_id (UK1)(FK)

            - member_id (UK2)(FK)

            - subscribed_on (UK3)

            - unsubscribed_on

             

             

            In MySQL, since both a Primary Key and a Unique Key (i.e. Unique Index/Constraint) behave identically, I still don't see why it is such a big deal to have the UK's on my ID fields and the PK's on Natural Keys (e.g. username, slug, etc).  Either way, I am making sure that the Surrogate and Natural Keys are unique by way of using a "key", right?

             

            Maybe it is just adhering to a convention, but physically you realize that - at least in MySQL - there is no difference in how the database would behave in either of these situations...

             

            - id (PK)

            - username (UK)

             

            and

             

            - id (UK)

            - username (PK)

             

            Sincerely,

             

             

            Debbie

            • 33. Re: Determining Keys for Junction Table
              jgarry

              This is not a mysql forum, so many of us don't care what it does.  There have been times we have been surprised that the Oracle optimizer is sophisticated enough to use additional information about the data from index information from indices not chosen to be used.  For just one example.

               

              So the translation from a pure normalized design to an implementation may bring up points like yours.  There are people who would chide you for doing that too, we're actually mostly pretty nice and helpful here.  That doesn't rule out having strong opinions, and widely varying opinions.

               

              I once had a computer instructor who would answer any question with "try it."  I still don't think he was very good, but he had a point.

              • 34. Re: Determining Keys for Junction Table
                Debbie_1004338a

                jgarry wrote:

                 

                This is not a mysql forum, so many of us don't care what it does.  There have been times we have been surprised that the Oracle optimizer is sophisticated enough to use additional information about the data from index information from indices not chosen to be used.  For just one example.

                 

                So the translation from a pure normalized design to an implementation may bring up points like yours.  There are people who would chide you for doing that too, we're actually mostly pretty nice and helpful here.  That doesn't rule out having strong opinions, and widely varying opinions.

                 

                I once had a computer instructor who would answer any question with "try it."  I still don't think he was very good, but he had a point.

                 

                I did listen to advice given here, and I did do my own research, and I did "try it".

                 

                It would help if people could respond to what I posted above in Post #26 and most recently Post #32...

                 

                While I did make some minor tweaks to what was in my OP, I still stand my ground and think a lot of this is "splitting hairs" versus completely going against established best practices.

                 

                As alluded to in Post #32, if you strip off the names "PK" and "UK", you would have no way of knowing which is which, and you would be able to do the same things regardless, which I feel only further proves my point...

                 

                But without people responding to my specific questions, who knows?!

                 

                Sincerely,

                 

                 

                Debbie

                • 35. Re: Determining Keys for Junction Table
                  jgarry

                  In Oracle, you can so find out which is the PK and which is the UK.  Easily.  Jeez.

                  • 36. Re: Determining Keys for Junction Table
                    jgarry

                    To quote a famous Oracle person, "Best Practices aren't."

                    • 37. Re: Determining Keys for Junction Table
                      Debbie_1004338a

                      jgarry wrote:

                       

                      In Oracle, you can so find out which is the PK and which is the UK.  Easily.  Jeez.

                       

                      Why do you insist on adding comments to this thread that have NOTHING to do with the conversation??

                       

                      Don't be a troll.

                       

                       

                      Debbie

                      • 38. Re: Determining Keys for Junction Table
                        jgarry

                        Yay! A newbie called me a troll!  My work here is done.

                         

                        I suggest you find Fabian Pascal and ask him your question.

                        1 2 3 Previous Next