1 2 3 Previous Next 36 Replies Latest reply on Mar 14, 2015 9:52 AM by johnnyb

    Relational Graph Bug ? many to many and get UUID

    ayescas

      If I have a one to many relationship using Auto Enter / Serial Number on the PK, the illustration on the relational graph is fine, one to many.

       

      Same relationship but instead of the Auto Enter, I’m now using a calculation Get(UUID) on the PK and the Relational Diagram show the relationship has many to many.  Why?  Is it a bug?

       

      On the relational graph it show as follows:

       

      Auto Serial Number: -|————<-

      Get(UUID): ->————<-

       

      -Alex

        • 1. Re: Relational Graph Bug ? many to many and get UUID
          electon

          I don't think it's a bug.

          |-----     Indicates that the match field is intended to match a single record, such as an auto-entered value

          Get(UUID) is officially not an auto enter value controlled by the field settings, it is rather a field that that can hold

          more than one value, like a value list. Usually a text field, which should be used for uuid anyway.

          The field is populated by an auto enter calculation, therefore not an auto entered value.

          I never look at those line ends much anyway. You shouldn't look at filemaker graph as an ER diagram.

          • 2. Re: Relational Graph Bug ? many to many and get UUID
            ch0c0halic

            The various symbols for the Relationship Graph are listed in the Help. "Working with the relationships graph" - "Other relationships graph elements".

             

            For example:

            |--- = Indicates that the join cannot be made. For example, a match field is missing or is an unindexed field (such as an unstored calculated field or a global field), or the table closest to the indicator is missing or unavailable

            • 3. Re: Relational Graph Bug ? many to many and get UUID
              Mike_Mitchell

              Since you used an auto-enter serial in the first instance, FileMaker knows it will be unique. Get ( UUID ) is simply an auto-enter calculation. Obviously, UUID implies that it will be unique, but FileMaker doesn't know that, so it guesses duplicates are allowed.

               

              Validate the field unique and you'll see the symbol change.

              • 4. Re: Relational Graph Bug ? many to many and get UUID
                mikebeargie

                To add to this, It's a many to many because UUIDs are stored as text. In FileMaker, relating two text fields will always result in the many to many line being displayed, since there is no auto-increment text type in the field definitions (only auto-increment serial in the number type).

                 

                A text field can store many things, like numbers, unique UUIDs or return delimited value lists. All can be used in valid relationships, but like Mike mentions above, FIleMaker doesn't know it's unique.

                 

                Same thing goes for 1:1 relationships. It will always show as 1:many since the child table foreign key is not defined as unique.

                • 5. Re: Relational Graph Bug ? many to many and get UUID
                  ayescas

                  thank you for the deep explanation and to recap I'm safe using get uuid for primary keys.  Is there any benefits using serial numbers vs uuid?

                  • 6. Re: Relational Graph Bug ? many to many and get UUID
                    mikebeargie

                    cross compatibility with other systems that aren’t expecting UUIDs as keys. For instance most MySQL databases are still running primary keys as incremental serials.

                    • 7. Re: Relational Graph Bug ? many to many and get UUID
                      Mike_Mitchell

                      Flip side being the annoyances associated with serial IDs, mostly with importing between copies (such as when upgrading a solution). So it's situational. Our SQL Server apps, for example, use UUIDs, so we tend that direction.

                      • 8. Re: Relational Graph Bug ? many to many and get UUID
                        ayescas

                        Thanks once again, It's my fist time using UUID as PK,  just want to make sure i'm heading in the right direction.  

                        • 9. Re: Relational Graph Bug ? many to many and get UUID
                          siplus

                          While in theory there's nothing wrong with using UUID as PK, there are lots of practical reasons not to do it. Here are some:

                           

                          - In a columnar report it will take too much space. Even on a normal layout.

                          - You can't search for a range, like PartNo 1500...1999

                          - When a client calls for details regarding an invoice, having to answer "0104B9AF-570E-45F3-B74B-6B84BC56B29B" to the question "what is your client number" or "what is the invoice number" is prone to errors and unpleasant

                          - In our specific case, we're sending invoices via XML files to the insurances; the XML schema allows only 35 chars for an unique identifier which we build by putting together patient ID and invoice ID and we couldn't do that with 2 36-char long IDs.

                          • 10. Re: Relational Graph Bug ? many to many and get UUID
                            BruceHerbach

                            When one side of a relationship has a | and the other a< it means the bar side is a global field. One value for all records in the table/occurrence. When both sides have >< it means you have a standard text or numeric field.  These include standard serial numbers (text or numeric ) and UUIDs. By the way a uuid can be in either a text or numeric field,  it is a hexidecal number.

                             

                            These days I use UUIDs for all primary keys.  However I will add a standard serial number/display ID when the user has to see it.  This field is not used in relationships but can be used for display and searches.

                            You do have the same serial number issues when importing data.  After all it is a serial number field.

                             

                            HTH

                            • 11. Re: Relational Graph Bug ? many to many and get UUID
                              Mike_Mitchell

                              You shouldn’t be worrying about most of this. Standard relational theory stipulates that a key field (a) carries no meaning, and (b) is not exposed to the user. So:

                               

                              - No exposing it on reports

                              - No searching for it by range (because it contains no meaning, and shouldn’t be exposed to the user anyway)

                              - …Same thing.   

                               

                              The only one of these that therefore should be a concern is the last one (having to do with XML). In that case, you could use an MD5 hash instead. As Bruce mentions, if you need an invoice number or client number or the like, then use a separate field.

                              • 12. Re: Relational Graph Bug ? many to many and get UUID
                                ayescas

                                thanks once again on the right path for sure.

                                • 13. Re: Relational Graph Bug ? many to many and get UUID
                                  siplus

                                  30 years ago I used to have professors, and they were delighted listening to that kind of theory. Was useful to pass exams.

                                   

                                  30 years later, I got clients.

                                   

                                  Summa summarum, if we get a project for the NSA I will obfuscate the PK's, if it comes from Joe's garage who wants to handle his clients, repairs and parts, I'll gladly use the PK's as identifiers, too. Let's be realistic.

                                  • 14. Re: Relational Graph Bug ? many to many and get UUID
                                    jbante

                                    Mike_Mitchell wrote:

                                     

                                    Standard relational theory stipulates that a key field (a) carries no meaning, and (b) is not exposed to the user.

                                    To pick nits, standard relational theory stipulates no such thing. A primary key only needs to be (1) unique and (2) immutable. It can carry meaning, be exposed to the user, even be a combination of multiple fields. In practice, making relational keys meaningless and inaccessible to end users avoids temptations to violate 1 & 2, but they are not themselves the rules. There are even situations where it's a good idea to making meaningful and user-visible keys that still comply with the actual rules.

                                     

                                    That said, in this case, I agree that keeping separate primary key fields and user-visible IDs separate is the appropriate way to go. It's important to maintain an independent linking between entities in your schema when integrating with other systems to avoid confusion and enable troubleshooting.

                                    1 2 3 Previous Next