1 2 Previous Next 15 Replies Latest reply on May 8, 2016 10:38 AM by Stephen Huston

    UUID v. Serial Numbers

    Abingdon

      I would be grateful to know folks' opinions on the pros and cons of using UUIDs rather than the 'traditional' FM serial numbers for the main id of a record.

       

      Are there any cons?  What should we be aware of that maybe we can do with serial numbers that we cannot do with UUIDs, or UUIDs rather than serial numbers?

       

      I admit this may be a no-brainer but I, and may be a few other people out there, would be interested to hear opinions on this.  Particularly in the case for converting an 'old' database to using UUIDs rather than existing serial numbers.

        • 1. Re: UUID v. Serial Numbers
          beverly

          Import (or any kind of "syncing") would need to have UNIQUE key fields. Auto-enter serial numbers must be handled with greater care if used and any kind of import or sync is required.

          beverly

          • 2. Re: UUID v. Serial Numbers
            coherentkris

            Text UUIDs as keys come with a greater indexing overhead than numeric keys

            • 3. Re: UUID v. Serial Numbers
              TomHays

              Can you quantify the overhead of using a text vs numeric indexed key?

               

              Is this a noticeable effect?

               

              -Tom

              • 4. Re: UUID v. Serial Numbers
                rmeier

                Hmm.. i don't think so.

                please give an example!

                 

                Filemaker stores everything as text at the end..

                • 5. Re: UUID v. Serial Numbers
                  Mike_Mitchell

                  It's been well-documented that FileMaker indexes number fields faster than text. See related links here:

                   

                  Re: UUID as decimal for faster performance

                  Key values - Best Practices - FileMaker Coding Standards

                  • 6. Re: UUID v. Serial Numbers
                    Mike_Mitchell

                    I generally prefer UUIDs, since serial numbers require careful management when importing records during upgrades. I've also never figured out a way to get them to work for a syncing solution where records can be created at both ends. (Maybe beverly has an example.)

                     

                    As Kris mentions, there is additional overhead associated with the text indexes. It's generally not an issue unless you're creating / importing / deleting large numbers of records on a regular basis.

                    • 7. Re: UUID v. Serial Numbers
                      rmeier

                      Hi Mike

                       

                      Thank you for the link, i will check this.

                      We develop since 2 years with UUID and for simple tables serial numbers.

                      With UUID we never had troubles..

                       

                      ok, and as i understand you, the speed of the relations is not affected. Ok, thats perfect for me.

                      large deletings run over night .

                       

                      cheers rico 

                      • 8. Re: UUID v. Serial Numbers
                        Mike_Mitchell

                        It slows down any operation involving indexing. The reason is text fields have both a value index and a word index. Number fields have only the value index.

                         

                        But as I said, the difference is usually a non-issue. I use UUIDs all the time, like you, and don’t generally have a problem.

                        • 9. Re: UUID v. Serial Numbers
                          greatgrey

                          Requirements Gatherer     

                            

                          If your Serial numbers only need to be unique and the records not in order enter just, use a serial number with a step of how many locations they are made from. i.e. Location1 100, 105, 110. Location2 101, 106, 111. Location3 102, 107, 112. Location4 103, 108, 114. Location5 104, 109, 114. Step of 5. Now a little math with a some location info could be done to keep from having to make a unique copy for each location. May not be the best way but it can work.

                          • 10. Re: UUID v. Serial Numbers
                            siplus

                            The record ID should not be visible and used as search string, at least that's what theory preaches. Makes sense.

                             

                            But when you have tables with millions of records and users do need to search for a case number, it might be a good idea to forget about theory and keep in mind the downtime caused by FMS verifying a database after a crash while 40 users can't work, just because you have 2 indexed keys instead of one and there's a couple of GB data more to inspect and verify for consistency . If you know you're gonna hit such magnitude, just use a numeric key and forget about UUID. And last but not least, users won't have to enter a UUID in their search field - just a long digit.

                             

                            My 2 cents, as usual.

                            • 11. Re: UUID v. Serial Numbers

                              Agree with beverly . UUIDs are "guaranteed" unique. If you're sharing data or importing other data where imported serial numbers could cause "collisions" (be the same as existing serial numbers), I'd opt for UUIDs. If they're a bit slower to generate, index, or import, who cares? You need to have data integrity first and foremost.

                               

                              As an aside.....I would be careful using "Natural Keys" (combination of fields from a record) as a unique key. Although natural keys can be OK, the business could change, fields could change, or any number of other things to make the current "natural key" no longer useful/correct or even ...  unique. This caution might not apply as much to FM since I don't think you have control over creating a named multi-field index as with other database environments.

                               

                              HOPE THIS HELPS.

                               

                              - m

                              • 12. Re: UUID v. Serial Numbers
                                BruceRobertson

                                "The reason is text fields have both a value index and a word index."

                                Only if that is how you actively choose to set them up. They can be set up with only value indexes.

                                • 13. Re: UUID v. Serial Numbers
                                  Mike_Mitchell

                                  Yes, this is true. Of course, FileMaker will create each of those indexes when needed unless you explicitly tell it otherwise

                                  • 14. Re: UUID v. Serial Numbers
                                    Abingdon

                                    Hi All,

                                     

                                    Firstly, many thanks for your replies, they are as always extremely helpful.  My reason for asking is I am working on an a database that I believe will have a number of versions, (clients cannot wait till it's the done deal can they!).

                                    It will unfortunately mean a lot of data exports and imports from existing version to new version(s).

                                     

                                    It's multi-file, multi table and will grow in complexity.  Although the numbers of records will not be large - tens of thousands, not hundreds of thousands, the thought of trying to keep track of serial numbers on data imports is pretty daunting.

                                     

                                    Also, as much of the data itself is coming in from external sources (Suppliers' Product ids, other apps etc.) that can range from 20 digit alpha-numeric codes to, brilliant ids like '01', then the chances of id clashes is pretty large.

                                     

                                    Therefore, I am intending on using UUIDs on the internal FM relationships without displaying them and 'external' supplied ids for Users to search on as that's what they are used to seeing.

                                     

                                    Thank you once again.  I am very proud that the big-hitters here have chosen to respond to what may have been a pretty obvious question.

                                    1 2 Previous Next