10 Replies Latest reply on Jul 28, 2014 2:02 AM by makerbert

    multi column primary key possible in FM?

    makerbert

      Hello, I'm quite new to filemaker but been fiddling with databases for 10+ years.

       

      I have to set up a database in filemaker. First table holds four (4) unique department names. Second table holds several thousands of subdepartment codes. subdepartment codes are not unique, but the combination departmentname-subdepartmentcode is unique.

       

      using Oracle I would create a one to many relation from the department table to the subdepartment table en a compound primary key consisting of the fk_departmentname and the subdepartmentname. Is something like that (primary key from two columns) possible in FileMaker or am i thinking in the wrong direction?

       

      Any views are welcome.

       

      Bert

        • 1. Re: multi column primary key possible in FM?
          jormond

          Kind of. You could concatenate the field to create the primary key.

           

          However, you could instead use FileMaker's UUID function. It gives you a Base 16 random unique id. In practice, I've found it often better to have IDs that have no meaning. It doesn't take up much room in the database, and you can easily, in the future and if needed, change department or sub-department names without having to worry about breaking the relationship.

          • 2. Re: multi column primary key possible in FM?
            erolst

            Joshua Ormond wrote:

            Kind of. You could concatenate the field to create the primary key.

            Multi-predicate relationship?

            • 3. Re: multi column primary key possible in FM?
              jormond

              That would give you a slightly different behavior. At least the way I'm familiar with the terms.

               

              1. concatenate:

              firstName: John

              lastName: Doe

              zip: 15551

              key = JohnDoe15551

               

              2. multi-predicate relationship:

              firstName: John

              lastName: Doe

              zip: 15551

              multi-predicate key = John¶Doe¶15551

               

              #1 would match the current record to any records in the related table where the match field is "JohnDoe15551"

               

              #2 would function like 3 separate joins. Returning all records that match "John" and also all the records matching "Doe" and also all the records matching "15551".

               

              For the sake of simplicity, if possible, I always use a UUID. Either FileMaker's UUID function, or Jeremy Bante's numerical UUID, or I suppose one day I'll convert FM's UUID to base10 and use that.

              • 4. Re: multi column primary key possible in FM?
                erolst

                Nope, that's just a “multi-key”, which is in fact an OR (the only way to bring OR into relationship definitions, AFAIK).

                 

                What I meant was “create a relationship with multiple pairs of match-fields (aka predicates)”, i.e. …

                 

                TableA::department = TableB::department

                TableA::subDepartment = tableB::subDepartment

                 

                … which as usual works as AND.

                • 5. Re: multi column primary key possible in FM?
                  jormond

                  Gotcha. I see what you were saying now.

                   

                  That would work from the Sub-department table. Though I would still opt for the UUID and just use that. Much less change for non-uniqueness.

                  • 6. Re: multi column primary key possible in FM?
                    keywords

                    Re Joshua's comment: "I've found it often better to have IDs that have no meaning".  That, as far as I am aware, is considered to be standard FM best practice.

                    • 7. Re: multi column primary key possible in FM?
                      jormond

                      While many agree with that, there are still many that do not. It's always a fun discussion. lol

                      • 8. Re: multi column primary key possible in FM?
                        erolst

                        Joshua Ormond wrote:

                         

                        While many agree with that, there are still many that do not.

                        On what grounds?

                        • 9. Re: multi column primary key possible in FM?
                          jormond

                          Reasons are as varied as the people that have them.

                           

                          Most of the time it boils down to they have never had to deal with a change to the business rules that determine the structure of the id they are using. For example, someone using the invoice number as the primary key...that are C100023 ( C for customer, 100 for the sales rep number and a serial number that resets each day ).

                           

                          Now management decides to change the sales rep numbers to something different.

                           

                          If you have ever had to deal with that on tens of thousands of records, you easily understand why meaningless UUIDs are best practice. If not, you don't see the need for the effort or extra field. Though I've heard a few wild excuses in the past.

                          • 10. Re: multi column primary key possible in FM?
                            makerbert

                            Thanks for commenting, I just returned on the parttime job so I didn't have the possibility to read them earlier.

                             

                            While in our environment it is very unlikely that subdepartments will change between departments I think I'll concatenate the name from fk_departmentname and subdepartmentname for human readability and use UUID for primary key.

                             

                            Bert