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

        1.) I like to have a numeric ID for ever record.

        Then go ahead and use surrogates for your primary keys and foreign keys. No one has said there is any issue with that if surrogates serve your use case best. We are just saying once you do that don't corrupt your data model by also using natural keys. Use one or the other but not both.

         

        Just remember that what you like isn't necessarily related to good architecture that uses normalized data models. Most of what you said in this thread is related more to what you WANT to do rather than what you SHOULD do.

         

        You aren't really seeking advice nor do you seem to be open to it. A database or data model is NOT about you or what makes development easier. It is about doing things in industry standard ways to makes things as performant and scalable as possible.

         

        IMHO you are really just seeking support and validation of decisions you have already made.

         

        Put yourself in my non-Oracle, non-enterprise situation a second...

        No thank you. This is an Oracle forum. The advice, though, stands on its own merits.

        Which table would you rather visually try and find a record in this table...

        That question is irrevelant. You can't 'visually' find a record in that table or any other table whether the database is Oracle, DB2, MySql or most any high-performance database.

         

        The data is stored in an internal binary format that is NOT scannable or useable by humans in its raw form.

         

        And business users certainly have no knowledge of, or interest in, a surrogate key value such as 1, 2, 3 and so on. Again, that shows you just don't understand the concept of surrogate keys. The fundamental attribute of a surrogate key is that it must NOT have a business meaning or purpose.

         

        So if a business uses natural keys that have values such as 'x3hdj59fnhjs34' then that value is EXACTLY what users should search for in the table. That is just plain common sense.

        But MySQL will display them like this since they are sorted by the Primary Key...

        Don't know if it will and don't care.

         

        Oracle is a database and is a server-side tool. What the client does depends on the client. But there is NO client that can display ANY Oracle data without first issuing a query of some sort that causes the database (which is on the server) to locate the data and return it to the client. That query can include an ORDER BY to return the result set in any order desired by the client.

         

        What the client then does with the data is of no concern to the database/server which has NO knowledge of it at all.

        4.) I might decide to make a field the Primary Key (e.g. "username"), and then with time realize that it changes too much and want to go with a Surrogate Key.

        And at that point you modify the data model to do things differently. You add the surrogate key columns needed and use them as the primary and foreign key, dropping the use of the natural keys for those purposes.

         

        That modified data model may even use natural keys for some tables and surrogates for others but no table will use or need both.

        But do they cause a problem with Data Integrity or Maintenance?  No.

        Yes - they do. That item #4 I just requoted above from you shows that they both can cause maintenance issues and do cause maintenance issues. That is EXACTLY why a developer should not do things based on their own preferences. The developer is NOT the business user. The developer is NOT the one that makes the decision about what can change or when it should change. In the corporate world the business users do that. That is why you need to anticipate what the BUSINESS USER might do unexpectedly and design your system to accomodate such changes without having to redesign the architecture.

        Is it really that black-and-white to you?

        Yes - it is.

        Don't you think I have some valid points above?

        No - I don't. And as I, and others, have repeatedly stated proper architecture and data model design is NOT about you and what  you want.

         

        Since you just want to do what you want then Joel already said it best: It doesn't really concern me. Just do it.

        • 16. Re: Determining Keys for Junction Table
          Debbie_1004338a

          >> 1.) I like to have a numeric ID for ever record.

          >

          > Then go ahead and use surrogates for your primary keys and foreign keys.

          > No one has said there is any issue with that if surrogates serve your use case best.

          > We are just saying once you do that don't corrupt your data model by also using natural keys. Use one or the other but not both.

           

          I know this is an eternally debated topic, but in YOUR opinion and experience, why would I want to use a Natural Key over a Surrogate Key?

           

          I think I understand both sides of the debate, but what is YOUR experience?

           

           

          > Just remember that what you like isn't necessarily related to good architecture that uses normalized data models.

          > Most of what you said in this thread is related more to what you WANT to do rather than what you SHOULD do.

           

          Nothing wrong with making my life easier since I am the Business/Architect/Developer/DBA... 

           

           

          > You aren't really seeking advice nor do you seem to be open to it.

           

          I'm open to advice, especially when someone explains why their way is better (versus "Just do it because I know more than you!")

           

          If I didn't care what other think, I wouldn't keep coming back for more abuse! 

           

           

          > A database or data model is NOT about you or what makes development easier.

          > It is about doing things in industry standard ways to makes things as performant and scalable as possible.

           

          I agree.

           

          I also know there are usually several approaches, and I'm looking for the best one.

           

           

          > IMHO you are really just seeking support and validation of decisions you have already made.

           

          Yes, I had hoped someone would say, "Looks great, Debbie."  (Obviously that isn't what I got...)

           

           

          >> Which table would you rather visually try and find a record in this table...

          >

          > That question is irrevelant.

          > You can't 'visually' find a record in that table or any other table whether the database is Oracle, DB2, MySql or most any high-performance database.

           

          When I'm looking at the database on the "server" using phpMyAdmin I certainly can "see" what is in the database.

           

          And that is how I interact with MySQL - using a GUI.  (No sin there.)

           

           

          > And business users certainly have no knowledge of, or interest in, a surrogate key value such as 1, 2, 3 and so on.

          > Again, that shows you just don't understand the concept of surrogate keys.

          > The fundamental attribute of a surrogate key is that it must NOT have a business meaning or purpose.

           

          And now you are not understanding me...

           

          Whether I use a GUI Client to access the Database, or Command Line, it is often easier to find a record scanning a Table, View, Query looking for an ID than some long cryptic Natural Key field.  (see example in my last post)

           

          I NEVER said the Business would see or use the ID.  (By definition, your Users shouldn't see a Surrogate Key.  So, YES, I do understand.)

           

           

          > So if a business uses natural keys that have values such as 'x3hdj59fnhjs34' then that value is EXACTLY what users should search for in the table.

          > That is just plain common sense.

           

          Again, you aren't following me.

           

          I never said anything about users.

           

          I said as I am developing and entering test data and evolving my architecture, it is easier for me to work with ID's that I obviously can see on the backend database.

           

           

          > Oracle is a database and is a server-side tool.

          > What the client does depends on the client.

          > But there is NO client that can display ANY Oracle data without first issuing a query of some sort that causes the database (which is on the server) to locate the data and return it to the client.

          > That query can include an ORDER BY to return the result set in any order desired by the client.

           

          If you are querying a Junction table with several keys in a Composite Key, and particularly if the table allows for duplicates, then that makes finding or discerning records awfully messy...

           

           

          >>  4.) I might decide to make a field the Primary Key (e.g. "username"), and then with time realize that it changes too much and want to go with a Surrogate Key.

          >

          > And at that point you modify the data model to do things differently.

          > You add the surrogate key columns needed and use them as the primary and foreign key, dropping the use of the natural keys for those purposes.

           

          So what would you recommend for my Member table.

           

          Truth be told, I actually have it defined like this, but posted it differently above for simplicity...

           

          MEMBER

          --------

          id (PK)

          email (UK)

          username (UK)

           

          I chose a Surrogate Key as the Primary Key, because while I do not allow Members to change their Username after registration, it *might* need to be changed.

           

          My website is not up, so I cannot speak from experience, but what if this happened...

           

          I get an e-mail saying, "Hey, can you fix my Username, I accidentally submitted my registration as 'DoubleDe' instead of 'DoubleDee'..."

           

          I thought purists say you should NEVER change a Primary Key?

           

          Should ID be my Primary Key or Username?

           

          Also, is it a problem to mix-and-match Surrogate and Natural Keys AS LONG AS THERE IS ONLY ONE PER TABLE??

           

          Maybe I want my MEMBER table to use a Surrogate Key, but for my ARTICLE table I like the readability that a Natural Key provides?

           

          Is that a problem?

           

          And would it be a problem for a Junction table like ARTICLE_THREAD to then have one Foreign Key that is a Surrogate and a second Foreign Key which is a Natural Key (e.g. 19, why-outsourcing-is-bad-for-your-business)?

           

          Sincerely,

           

           

          Debbie

          • 17. Re: Determining Keys for Junction Table
            Hemant K Chitale

            Initially, you posted this definition :

            member

            - id (UK)

            - username (PK)

             

            Now, you've posted

            MEMBER

            --------

            id (PK)

            email (UK)

            username (UK)

             

            So, you've switched the keys.

            As for "I thought purists say you should NEVER change a Primary Key",  it is a bad idea to have a PK that keeps changing.  If you define a rule that usernames do not change (and in most corporates, the username you are given does NOT change), the username could be the PK.  If you do want to allow it to change, yes you make it a UK and have a surrogate UserID as the PK.  For example, in an EMPLOYEES table, EMPLOYEE_ID is the PK.

             

            Hemant K Chitale

            • 18. Re: Determining Keys for Junction Table
              Debbie_1004338a

              HemantKChitale wrote:

               

              One uses Surrogate Keys  (your "ID" keys) only when one cannot find a Natural Key in the data.  You do have natural keys (username, slug) for the first two tables.  Ergo, you don't need the surrogate keys for the first two tables.  But you likely need it for the article_thread table.

              Furthermore, any foreign keys should be referencing these primary key columns. That means that the FK definition for the article_thread table is wrong.

               

              Hemant K Chitale

               

              If I have one Primary Key - either Surrogate or Natural - in each Parent Table, and in my Junction Table, I have a Foreign Key which maps back to said singular Primary Key in each Parent Table, then is it a problem to also have a Surrogate Key in a Junction Table?

               

              For example...

               

              MEMBER

              -------

               

              id    email            username

              ---    ------            ---------

              19    debbie@mail.com        DoubleDee

              25    bob@mail.com        BigBadBob

              37    fred@mail.com        ReadyFreddie

               

               

               

              ARTICLE

              --------

               

              slug                    body

              -----                    -----

              hiring-good-it-people-pays-off        xxxxxx

              should-you-outsource-customer-service    xxxxxxxx

              why-you-need-a-cpa            xxxxxxxxx

              benefits-of-becoming-an-s-corp        xxxxxx

               

               

              SUBSCRIPTIONS

              --------------

               

              id    member_id    article_slug

              ---    ----------    -------------

              1    19        why-you-need-a-cpa

              2    37        hiring-good-it-people-pays-off

              3    19        should-you-outsource-customer-service

               

               

               

              Sincerely,

               

               

              Debbie

              • 19. Re: Determining Keys for Junction Table
                Hemant K Chitale

                Given the values of "slug" that you show in the latest example, I wouldn't set slug as the PK but would use a surrogate key SLUG_ID.  There is no guarantee that you wouldn't have two articles with the same title (say in different publications or by different authors).  So the article title (which I assume is the "slug") cannot be the PK. It might not even be unique unless you apply some transformation to the article title to generate uniqueness.

                Is Subscriptions your junction table ?  Yes, I would have SUBSCRIPTION_ID  (which seems to be a likely  Natural Key ?!) and MEMBER_ID and SLUG_ID (as I have described above).

                 

                Hemant K Chitale

                btw : for my understanding, what is a "slug" ?  A quick search on google reveals different meanings of "slug", none of which is representative of the article names you list.

                • 20. Re: Determining Keys for Junction Table
                  Debbie_1004338a

                  > So, you've switched the keys.

                   

                  Yes, in the post above yours, I said, "Truth be told, I actually have it defined like this, but posted it differently above for simplicity..."

                   

                   

                  > Given the values of "slug" that you show in the latest example, I wouldn't set slug as the PK but would use a surrogate key SLUG_ID.

                  > There is no guarantee that you wouldn't have two articles with the same title (say in different publications or by different authors).

                  > So the article title (which I assume is the "slug") cannot be the PK.

                  > It might not even be unique unless you apply some transformation to the article title to generate uniqueness.

                   

                  This is my website, and in addition to being Owner, Architect, DBA, and Developer, I am also the Editor!!!

                   

                  So, YES, each Article Title and Article Slug will always be unique.  (If I forget that I already used "should-you-outsource-customer-service", then MySQL will let me know, and I'll just have to be creative and come up with another name.)

                   

                  I don't see the article_slug being unique being an issue.

                   

                  Also, since I am the only one writing Articles, I don't see the need to change the article_slug short of a type-o.

                   

                  I am somewhat concerned about using a VARCHAR(100) as a Primary Key for performance reasons...

                   

                   

                  > Is Subscriptions your junction table?

                   

                  "Subscription" is the same as "Article_Thread", and YES, that would be the Junction Table.

                   

                   

                  > Yes, I would have SUBSCRIPTION_ID  (which seems to be a likely  Natural Key ?!) and MEMBER_ID and SLUG_ID (as I have described above).

                   

                  To clarify, "subscription.id" would be a Surrogate Key.

                   

                  Technically I would not need "subscription.id" in a Junction Table, and could just have "member_id" + "article_slug" + "subscribed_on" be the Primary Key.

                   

                  That was one of my questions in my Original Post which we have never gotten to...  It's in BOLD in the OP.

                   

                  For a Junction Table, which of these is acceptable...

                   

                  OPTION #1:

                  SUBSCRIPTIONS

                  --------------

                  - member_id (FK)

                  - article_slug (FK)

                  - created_on

                   

                   

                   

                  OPTION #2:

                  SUBSCRIPTIONS

                  --------------

                  - member_id (PK1)(FK)

                  - article_slug (PK2)(FK)

                  - created_on (PK3)

                   

                   

                   

                  OPTION #3:

                  SUBSCRIPTIONS

                  --------------

                  - id (PK)

                  - member_id (FK)

                  - article_slug (FK)

                  - created_on

                   

                   

                   

                  OPTION #4:

                  SUBSCRIPTIONS

                  --------------

                  - id (PK)

                  - member_id (UK1)(FK)

                  - article_slug (UK2)(FK)

                  - created_on (UK3)

                   

                   

                  (I like Option #4 the best...) 

                   

                   

                   

                  > btw : for my understanding, what is a "slug" ?

                  > A quick search on google reveals different meanings of "slug", none of which is representative of the article names you list.

                   

                  "Slug" is a term used to describe the bolded string you find in a "Pretty" URL like this....

                   

                  www.debbie.com/management/should-you-outsource-customer-service

                   

                   

                  (See this page for more details...  Clean URL - Wikipedia, the free encyclopedia )

                   

                  Sincerely,

                   

                   

                  Debbie

                  • 21. Re: Determining Keys for Junction Table
                    Hemant K Chitale

                    I still think that SLUG cannot be the PK because there really is no guarantee that two articles won't have the same name.  Tomorrow, someone else might own the site or there may be additional contributors to the site or you may write an article for which you really cannot think of a name that is different from all the existing ones.

                     

                    The subscriptions table seems to identify subscribers.  I would use SUBSCRIPTION_ID for each distinct subscription.

                     

                     

                    Your responses so far on this thread are along the lines "I am right and not open to changing my data model".

                     

                     

                    Hemant K Chitale

                    • 22. Re: Determining Keys for Junction Table
                      Debbie_1004338a

                      > I still think that SLUG cannot be the PK because there really is no guarantee that two articles won't have the same name.

                      > Tomorrow, someone else might own the site or there may be additional contributors to the site

                      > or you may write an article for which you really cannot think of a name that is different from all the existing ones.

                       

                      Any Natural Key is susceptible to changing...

                       

                      > The subscriptions table seems to identify subscribers.

                       

                      No, it identifies SUBSCRIPTIONS (to ARTICLES made by MEMBERS).

                       

                      "Subscribers" are identified in the MEMBER table.

                       

                       

                      >  I would use SUBSCRIPTION_ID for each distinct subscription.

                       

                      But that doesn't tell me how to handle the keys...

                       

                      Which of the 4 Options above do you think is best???

                       

                       

                      > Your responses so far on this thread are along the lines "I am right and not open to changing my data model".

                       

                      Why do you say that?????????????

                       

                      I just offered four scenarios I could think of for this latest Junction Table and then I asked which one YOU think is best...

                       

                      Sincerely,

                       

                       

                      Debbie

                      • 23. Re: Determining Keys for Junction Table
                        Hemant K Chitale

                        Since we're now talking of only the SUBSCRIPTIONS table, I would go for Option 4.

                         

                        Hemant K Chitale

                        • 24. Re: Determining Keys for Junction Table
                          Debbie_1004338a

                          I'll reply to Hemant after I hopefully get some answers to my questions to rp0428 in my post dated Sept 23, 2013 3:01am

                           

                          (HINT: Admins, it would be nice if Posts on this board had Post #'s so you can more easily reference things!!)

                           

                          Sincerely,

                           

                           

                          Debbie

                          • 25. Re: Determining Keys for Junction Table
                            jgarry

                            (The posts do have numbers if you click on the thread name and look at them that way.)

                             

                            I'm glad rp and Hemant have responded to this thread, they tend to be very clear about the issues.

                             

                            Of course you can do whatever you want with articles that you write and deliver, but I must say online articles really should have things like versioning, modification tags and date stamps.   That's even my complaint about Oracle docs and support notes.

                             

                            Funnily enough, my cousin literally wrote the book on Subchapter S corporations (one of your examples, for those who may not have noticed) - and makes quite a good living writing articles and giving lectures on all the changes.  That is the most important point when dealing with information, hard or soft - it changes.  Even dusty old historical information.  Web articles that don't give you enough information to evaluate their honesty about this should be ignored.  Trust | Oracle Scratchpad

                            • 26. Re: Determining Keys for Junction Table
                              Debbie_1004338a

                              My thread is morphing... 

                               

                              I read everyone's responses - especially rp0428's - last night and am always willing to consider news ways to do things.

                               

                              So let's say I agree that each Table should only have one "key".  It could be a Surrogate Key or a Natural Key.  Doesn't matter.  But there should only be one.

                               

                              Sounds good, except for this problem...

                               

                              Let's use my MEMBER table as an example.

                               

                              Since I have this "thing" for needing an ID to easily reference things, let's say I choose this design...

                               

                              MEMBER

                              - id (PK)

                              - email

                              - username

                              - and so on...

                               

                              Looks good so far, but wait...

                               

                              What is to prevent this from occurring?

                               

                              id    email   username
                              ---    ------   ---------
                              1    debbie@mail.com   DoubleDee
                              2    debbie@mail.com   DoubleDee
                              3    debbie@mail.com   DoubleDee
                              4    frank@mail.com   FreakyFrank

                               

                               

                               

                               

                              Well, what I would do is simply add a Unique Index (UK) to both "email" and "username", so I would have this...

                               

                              MEMBER

                              - id (pk)

                              - email (UK)

                              - username (UK)

                              - and so on...

                               

                              SIDE NOTE: This may have been causing some of the "communication breakdown".  In MySQL, an "index" is both an "index" AND a "constraint".  So when I say "Unique Index (UK)" that means that MySQL is placing an Index on the field as well as a Constraint to ensure uniqueness.  As I recall, Oracle has a "Unique Constraint" but just generic "Indexes".

                               

                               

                              Okay, great!  So now my table prevents having duplicate Emails and Username, and I have a Primary Key that is a Surrogate Key.

                               

                              BUT WAIT A MINUTE... 

                               

                              Now my table has a Primary Key (PK) plus two Unique Indexes (UK) which - as described above - serve as "keys" as well.  SO NOW MY TABLE HAS THREE KEYS?! 

                               

                              And isn't that just the "flip" of what I posted in my Original Post which was this...

                               

                              MEMBER

                              - id (UK)

                              - email (UK)

                              - username (PK)

                              - and so on...

                               

                              Here are the BUSINESS RULES...

                              - Every Member Email must be unique

                              - Every Member Username must be unique

                              - Emails can be changed

                              - Username currently cannot be changed, but may conceivably need to be changed by an Admin

                              - The Member ID can be some arbitrary number as long as it is unique and never-changing.

                               

                              I AM TRYING TO TAKE PEOPLE'S ADVICE, but it brings me back exactly to where I was when I created this thread...

                               

                              If I go with a Surrogate Key and make that my Primary Key (PK), then fine, I have one key.

                               

                              But the minute I add in the Business Rules that Emails and Usernames must be unique, AND that Usernames could change, then that forces me to add a "Unique Index" (think "Unique Constraint" in Oracle) AND THUS I HAVE MORE THAN ONE KEY!!!

                               

                              There is NO WAY to have it both ways based on what I am describing.

                               

                              And that is why I chose the design that I have, which didn't, and still doesn't seem so "evil"...

                               

                              See where I am coming from, rp0428???

                               

                              Sincerely,

                               

                               

                              Debbie

                              • 27. Re: Determining Keys for Junction Table
                                jgarry

                                In Oracle, the unique index is not necessary.  It does help the performance, usually.

                                 

                                But you only have one primary key, so what exactly is the problem?  Does the surrogate key mean you violate Boyce-Codd normal form?  Or are the others just another constraint?

                                • 28. Re: Determining Keys for Junction Table
                                  Debbie_1004338a

                                  jgarry wrote:

                                   

                                  In Oracle, the unique index is not necessary.  It does help the performance, usually.

                                   

                                  But you only have one primary key, so what exactly is the problem?  Does the surrogate key mean you violate Boyce-Codd normal form?  Or are the others just another constraint?

                                   

                                  If you truly want to help out, then go back up to the Original Post and read all the way down to here.

                                   

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

                                   

                                  I have never felt that way, and regardless, if I am doing something of so wrong, then someone please answer Post #26 and tell me how to fix things...

                                   

                                  Sincerely,

                                   

                                   

                                  Debbie

                                  • 29. Re: Determining Keys for Junction Table
                                    Hemant K Chitale

                                    >Now my table has a Primary Key (PK) plus two Unique Indexes (UK) which - as described above - serve as "keys" as well.

                                     

                                    There's nothing wrong with that.  An Employees table could have EMPLOYEE_ID as the PK with an additional Unique constraint on EMAIL_ADDRESS.

                                     

                                    Hemant K Chitale