1 2 3 Previous Next 40 Replies Latest reply on Nov 15, 2012 5:37 PM by anthony@littleman.com.au

    The Best Primary Key values

    JayIchiyen

      I have been wrestling with the best Primary Key values for my database solutions.

      A developer once told me to create a calculated key from two fields: 1) a text field and 2) a number field. The text field is a two to four letter abbreviation of the table name ex. INV for the Invoice table. Secondly, a serial number field using the Auto-Enter serial number option. The primary key is a concatenation of these two fields with the number field having the appropriate number of leading zeros. Seems like a lot of work. I’m sure they explained the benefits of this to me at one point but I can’t recall why. Maybe for sorting?

      Why wouldn’t I just use one primary key field with an Auto-Enter Serial option that already has the table name in it? Ex INV0001 increment by 1.

      Plus, like in Access, why wouldn’t I just use a serial number as I hear that number fields are faster than text fields? I guess having some text would be helpful to identify the ID field data if it were exported and to give it some meaning.

      What do you use for primary key values?

        • 1. Re: The Best Primary Key values
          comment

          JayIchiyen wrote:

           

          why wouldn’t I just use a serial number

           

          Indeed, why not? In some special cases, adding a table code to the ID may be beneficial - but those are exceptions, not the rule.

          • 2. Re: The Best Primary Key values
            BruceHerbach

            When you set up a primary key you can set it up as a text field with a text part and a numeric part.  As in TXT00001 and filemaker will happily increment the numeric part for you.  So you can still do this with just one field.

             

            Another option in 12 is to use the new get(UUID) to populate the primary key.   The advantage to this is that you end up with a unique ID so it works directly with programs like 360works MIrrorSync.  The disadvantage is you can't look at a set of IDs and determine which record came first or anything else you would do with a sequential ID.  Note MirrorSync will work with either type of ID, the UUID does simplifiy some things.  You can also come up with ways to determine which record came first when using a UUID for the primary key.  You just have to put this in place first.

             

            In short both have uses.

             

            Bruce

            • 3. Re: The Best Primary Key values
              Shauniedarko

              This might sound nutty, but I do both.  I use the Get(UUID) as my primary key in every table to make sure that I always have a unique ID.  Then each table has its own prefix followed by a serial number.  So contacts would have CON111, Companies would have COM111  ect.

               

              The reasoning is so that the PKID will always be unique, but I don't have to show it to users.  They can refer to the nice looking record ID.  It also helps me identify if a record is missing.  The downside to Get(UUID) is that the numbers are sequential.  So if a record goes missing, it's a little more difficult to track it down.  Using both UUID and a seqential ID eliminates that problem.

               

              But to answer the question, I think Get(UUID) provides the safest choice for a primary key.

               

              S.

              • 4. Re: The Best Primary Key values
                comment

                Shauniedarko wrote:

                 

                I think Get(UUID) provides the safest choice for a primary key.

                 

                Actually, the safest choice is a serial number that cannot repeat. A UUID is a random number, and though it is very unlikely, it can repeat itself. Some people do win the lottery.

                • 5. Re: The Best Primary Key values
                  mark_scott

                  There's an excellent discussion of primary key strategies (mostly centered around various flavors of UUID) over at FileMaker Standards.org, which may be of interest to you.

                   

                  Mark

                  • 6. Re: The Best Primary Key values
                    Shauniedarko

                    If you set the field to validate as always unique, you won't have to worry about the unlikely event that a UUID will repeat itself. 

                     

                    My problem with the serials in FileMaker is that they CAN repeat. Not by themselves.  They're set up to increment by any number you choose, which should ensure that they don't duplicate, but you can run into problems if people import incorrectly or migrate data without resetting the counter.  In any case, the validation for uniqueness should be on whichever method you choose, but I still think that UUIDs are inherently less likely to produce duplicate values than serials.

                     

                    But, to each his own.

                    • 7. Re: The Best Primary Key values
                      Shauniedarko

                      Thanks   I actually read that a while back. 

                      • 8. Re: The Best Primary Key values

                        Please see <http://fmdiff.com/fm/serialnumber.html> for a detailled explanation.

                         

                        Winfried

                        • 9. Re: The Best Primary Key values
                          Shauniedarko

                          Nice explanation.  However, I'm curious why you recommend creating a custom function (which would have to be imported or recreated in every new file created) rather than Get(UUID) which is native to FileMaker.  Your RecID&Timestamp method requires more setup (though extremely marginal) and is probably no more statistically probable to result in a duplicate record than a UUID.  I'm not suggesting isn't not a good method, simply curious why you would recommend one over the other.

                          • 10. Re: The Best Primary Key values
                            jbante

                            Different UUID schemes have different performance characteristics, as discussed in this thread. Number-type primary key fields are faster to do finds on and take less space to store. There's also more than one way to make a UUID. FileMaker 12's built-in Get ( UUID ) function produces a random type UUID, which has an astronomically small but non-zero chance of randomly making a duplicate. Field validation for uniqueness mitigates the risk at some performance cost. UUID schemes based on timestamp and one form of device ID or another are less likely to create duplicates, but the random type is more commonly used due to privacy concerns. There are also UUIDs that meet half-way, using a timestamp and a session-specific random number in place of any device ID. Custom function-based UUIDs let developers optimize the characteristics most important to them.

                            • 11. Re: The Best Primary Key values

                              Simply because I assume you have more than one table in a file. This is just a suggestion, you may as well put the same auto-enter calculation in every key field if you prefere.

                              • 12. Re: The Best Primary Key values
                                comment

                                Shauniedarko wrote:

                                 

                                 

                                My problem with the serials in FileMaker is that they CAN repeat. Not by themselves.  They're set up to increment by any number you choose, which should ensure that they don't duplicate, but you can run into problems if people import incorrectly or migrate data without resetting the counter.  In any case, the validation for uniqueness should be on whichever method you choose, but I still think that UUIDs are inherently less likely to produce duplicate values than serials.

                                 

                                You are speaking of implementation, not method.

                                • 13. Re: The Best Primary Key values
                                  Shauniedarko

                                  I wasn't questioning why you'd create a custom function, but rather why you'd create a custom function over using a native function.

                                  • 14. Re: The Best Primary Key values
                                    Shauniedarko

                                    That's an interesting discussion.  I'd say though, that the takeaway is that nothing is infallible.  The difference between an astronomically small chance and a really astronomically small chance is pretty small.  Too small, in my opinion, to make any method bulletproof, and I'd still take the precaution of validating for uniqueness.  I'm not sure the performance hit is noticiable enough to warrant risking a duplicate ID value.


                                    But that's just me. 

                                    1 2 3 Previous Next