10 Replies Latest reply on Jun 29, 2010 3:34 PM by LaRetta_1

    Generally, Do Child Tables need Primary Key?

    eibcga

      Title

      Generally, Do Child Tables need Primary Key?

      Post

      Do you recommend that a Child table have a primary key field?  I have a child table with a foreign key field that links to the parent key in the Parent Table in a one-to-many relationship.

       

      For example, I have a database that tracks financial transactions.  The JOURNAL (a Child Table) has a primary key field added to it (auto-enter serial), but I'm not sure it will be required (since I don't foresee it ever relating to a foreign key in another table in the future).

       

      Tables:

      NAMES

      TRANSACTIONS aka JOURNAL

      TRANSACTION LINE ITEMS aka LEDGER

      ACCOUNTS

       

      thanks

       

       

       

       

        • 1. Re: Generally, Do Child Tables need Primary Key?
          eibcga

          PS: I was trying to add an image (screen shot) of my ERD to this post, but cannot seem to figure that out, even though I have seen other posters on this Forum do that.  Some hints to point me in the right direction would help.  I tried cutting and pasting from "Word", which apparently didn't work.

          • 2. Re: Generally, Do Child Tables need Primary Key?
            LaRetta_1

            EVERY table should have a Primary Key and don't let anyone tell you otherwise.  It is simple to add a primary key when you create a table ... it is more difficult to add a primary key later when you realize you should have added one to begin with.  Primary keys (auto-enter, FM-generated numbers) take very little space.

             

            Every time I have thought that I didn't mean a primary key, I have had to add one later for further relationships OR ... and this is even worse ... I have to pull records from a backup and 'import update and add to' depending upon what?  If you have no primary key, you can't retrieve information.  Make it important habit and first thing you always put in EVERY table. Add to that list, a creatime timestamp and modification timestamp.  And you usually also want created by and modified by. 

             

            Creation and modify are important if something goes wrong - you can find records according to last modified - you can find the person who added the junk into a field.  This is all very important; primary keys are NOT just for relationships.

            • 3. Re: Generally, Do Child Tables need Primary Key?
              eibcga

              Correction:

              the LEDGER table (also a 'join table' as it has a primary key field, and two foreign key fields) is the Child Table to the Parent Table called JOURNAL.

               

              NAMES table (i.e., of customers and suppliers) is the parent table to the Child Table called JOURNAL.

               

              ACCOUNTS table is the Parent Table to LEDGER table.

              • 4. Re: Generally, Do Child Tables need Primary Key?
                philmodjunk

                eibcga wrote:

                PS: I was trying to add an image (screen shot) of my ERD to this post, but cannot seem to figure that out, even though I have seen other posters on this Forum do that.  Some hints to point me in the right direction would help.  I tried cutting and pasting from "Word", which apparently didn't work.


                Here's a link describing step by step how to post a picture to your forum messages:

                Tutorial-How to post a picture so that it shows up in your message


                • 5. Re: Generally, Do Child Tables need Primary Key?
                  FentonJones

                  Just to be contrary, I have to disagree with the blanket statement that EVERY table needs a primary id. I occasionally have tables which do not have one. Examples: reference tables, City & Zip Code, or a few records that I use to map access to tables (so my scripts can know which a group has access for navigation), a Months table, a Weeks table, a Dates table, 1 record tables (globals, constants), etc.. In other words, special use tables outside the usual realm of "user entered data", but which are still useful to the database.

                   

                  Also (more controversial) a table occasionally which is just the end of the road, is never accessed in any way which requires a primary id; though sometimes an id must be added later, via Replace. I would know when I needed it, and add it. Since I never needed it before, it is not a problem. 

                   

                  Notice that these are all "occassional". But the existence of even one negates the blanket statement. The idea that you must have a primary id for every table may be useful, to encourage beginners to use IDs, which is a good thing. But that does not make it true, in my opinion. I think every developer should know when they need one (almost always), and when they do not. They need to think about it, what its uses might be.

                   

                  I would encourage creation Timestamps in almost all tables also (except those like in my 1st paragraph). 

                   

                  In large systems with a lot of tables, I use a mixture of regular FileMaker auto-enter serial IDs and UIDs (auto-entered unique IDs, text & number, long and pretty much unreadable). The regular ones are best for anything where you really want to see the IDs for any reason, even for troubleshooting. The UIDs are for tables where you need a primary key, but it is a "near end of road", seldom used.

                   

                  The UIDs spare me from having to do the "update serial id option to next value" in automated "restore data to a clone" scripts, which all large systems should have. I only have to update the regular ones, as a UID is unique as is. 

                   

                  I use Ray Cologon's: http://www.nightwing.com.au/FileMaker/demos9/demo910.html

                   

                  So while it is true that you almost always need a primary id, it is not true that, for all databases, you always need a primary id in every table. In all cases you need to think hard about it. 

                   

                   

                  • 6. Re: Generally, Do Child Tables need Primary Key?
                    LaRetta_1

                    If you can find ONE exception, I suppose I shouldn't have said it so strongly but I still mean every word.  One may say strongly, "do not run with scissors" and there may be an exception if a bear is chasing you, but it STILL should be said strongly.  The time one usually realizes (gets that cold, sinking feeling) that they should have had a primary key is when they need to retrieve data from backup in one field while preserving data in the other fields and they don't have one.   And later you may decide to add a field which holds user data and you may forget this is the ONE time you didn't add a primary key.

                     

                    Then I will change my statement to this:  Beginner or not, if you EVER can change data in even ONE field even ONCE then you should have a primary key.  You cannot depend upon joining on multiple fields to retrieve the one damaged field's contents because you usually cannot guarantee the data in one of the other fields hasn't changed.  And if you simply revert to backup, you have lost that ONE change in another field (and every data change is important). 

                     

                    Can you honestly say that you haven't regretted NOT having a primary key when you needed to retrieve partial data from backups?  I have. It is like seat belts; you may never need them but if you did ...  and it takes so little to click the seat belt on and it takes so little to add a primary key.

                     

                    Update: Corrected typos

                     

                    • 7. Re: Generally, Do Child Tables need Primary Key?
                      eibcga

                      Thank you to all very much.

                      • 8. Re: Generally, Do Child Tables need Primary Key?
                        FentonJones

                        I concede the point, that if you had to restore 1 field from a backup, then a primary key may be required (though you could likely use a combo of a foreign ID and a creation timestamp; but if you couldn't, you would, as you say, regret it). But I have never had to do that; it must be pretty rare. It's hard to imagine a damaged field which would not mean a damaged file, in which case you'd need to import all the data into a clone. 

                         

                        Though it is certainly true that a user (or developer) could "damage" the data, via an operation which messed it up.

                         

                        But there are are still exceptions, such as single record tables, and a few other smallish "reference" tables, with only a few records, where a primary ID is just unnecessary. Some people even have entire tables just to produce consistent layouts (in an interface file). Hence I object whenever developers make blanket statements. I'm just annoying that way. 

                         

                        In my mind, primary keys elicit thoughts about future automated Import routines into clean clones. Every regular FileMaker auto-enter ID must be updated correctly for the "next value". Hence I use UIDs when I can, and don't add primary keys to tables where I cannot conceive needing them (very few).

                        • 9. Re: Generally, Do Child Tables need Primary Key?
                          eibcga

                          I have tried to share my ERD here using your suggestion, but I get a blue box with a white question mark after I have entered the image URL and clicked the Insert button after using the Insert/Edit Image (tree) icon.

                           

                          The link http://www.4shared.com/photo/IzOlqfzp/Screen_shot_2010-06-29.html?

                          • 10. Re: Generally, Do Child Tables need Primary Key?
                            LaRetta_1

                            Fenton said, "Though it is certainly true that a user (or developer) could "damage" the data, via an operation which messed it up."

                             

                            Sure.  And it is also true that one might have been biten by the spontaneous mapping import bug and wiped out data in one field which wasn't noticed for a week.   We are not immune from all issues (no matter how hard we try to protect and believe me I do), whether it is a mistake we make (and yes, I can make mistakes and so can you) or whether external sources or bugs (and yes there are some which can affect data) .

                             

                            Sometimes a User may have power-user rights ... well, simply,  _hit happens.  And when it does, the decision needs to be made whether to lose all the information entered in other fields (by going to a morning backup for instance) or by simply updating one field with its backup data.  If one gets lack or self-forgiving (lazy) about this issue, it will bite you in the no-sunshine zone.

                             

                            You are correct to point out that nothing is absolute and no, one-record tables aren't at issue here.  And I appreciate you speaking up.