5 Replies Latest reply on May 5, 2014 2:43 PM by philmodjunk

    Multi Comun Key Alternative

    BenN

      Title

      Multi Comun Key Alternative

      Post

           I am new to Filemaker; I used flavors of SQL in the past. Using these environments I could create indexes and keys based on multiple fields. This was the logical way to build and worked great for millions of records for decades without any problems. From what I have read in this forum that is frowned upon in Filemaker. Now that I am using FM I want to use best practices for the program but my experience has my brain locked into one way of thinking and I can't seem to come up with the best way to proceed. Here is what I am trying to accomplish-

            

      ItemsDB

             
      •           ID (text - unique and set by our web interface)
      •      
      •           Name
      •      
      •           ...

      SaleHeaderDB

             
      •           SaleID (Unique)
      •      
      •           Name
      •      
      •           StartDate
      •      
      •           EndDate
      •      
      •           ...

      SaleDetail

             
      •           SaleHeaderID (From SaleHeaderDB)
      •      
      •           ItemID (From ItemsDB)
      •      
      •           SalePrice
      •      
      •           ...

           We have ~40,000 items and weekly sales (sometimes twice per week) that normally contain about 10% of our items (sometimes all our items). After a year an item may be included in 30 different sales with different sale prices. I want to be able to create a new sale in the SaleHeaderDB and then add items to SaleDetailDB from our ItemsDB. The combination of SaleHeaderID and ItemID will be unique in the SaleDetailDB. Previously this is what I would have used as the index and would have done something like: INSERT INTO SaleDetailDB (SaleHeaderID, ItemID) VALUES (#, ItemsDB:ID). With no additional tables and a single line of code records can easily be created, modified, queried, etc. The simplicity and low overhead is something I really really like.

           From what I have read I am thinking I have to create a new Join table between SalesDetailDB and ItemsDB. Then when I want to add an item to a sale I need to script a layout change (two layout changes with the join?) to add the records and then reverse the layout change back to my original screen? My items layout already has a lot of filters (deleted product, pending items, non-item items...) so would these filters have to be refreshed every time I switch to layouts and came back? This seems bulky and cumbersome to setup, maintain and use so I am hoping I am missing something (I often am).

           Any advice on on how to structure my tables and achieve what I need while still following FM's best practises would be greatly appreciated. Thanks in advance.

            

           P.S. My fall back plan is to concatenate to two fields into an index but this make relationships tough to manage in FM.

           P.P.S. The 2 Sale Databases are new at this point so if there is a better way these can be rebuilt or scrapped.

        • 1. Re: Multi Comun Key Alternative
          philmodjunk
               

                    From what I have read in this forum that is frowned upon in Filemaker.

               I would not use such a key as the PRIMARY key in any relational database, not just FileMaker.

               I do not see the need for any added table in your database. The typical sales system in FileMaker looks like this:

               Invoices-----<InvoiceData (also called "lineItems")>-----Products

               Changing the names to match your example produces the same structure:

               SaleHeaderDB-----<SaleDetail>------ItemDB

               SaleHeaderDB::SaleID = SaleDetail::SaleHeaderID
               ItemDB::ID = SaleDetail::ItemDB

               No additional table needed and I do not see any obvious reason for combining IDs from multiple fields to do what you describe here. In most FileMaker database solutions, SaleHeaderDB::SaleHeaderID and ItemDB::ID would be number fields with auto-entered serial numbers, though they can also be text fields that auto-enter Get (UUID) with Unique Values specified.

          • 2. Re: Multi Comun Key Alternative
            BenN

                 I am very glad another DB isn't needed, that is excellent news.

                 My experience is in much more programmer-centric environments vs. Filemaker's user-centric approach. By default Filemaker has all the options open to any user, I am used to users only having the power I build and deploy to them. The multi-field indexes were all batched with no user interaction, or user visibility. Filemaker gives so much default power to the end user it will take some adjustments to my thinking and design.

                 I still have a lot to learn in FM- I just hit the point where I can be dangerous! The Get functions seem to be pretty powerful but I haven't used any yet.

                 Thanks so much for your reply and advice.

            • 3. Re: Multi Comun Key Alternative
              philmodjunk

                   I can't quite picture what:

                   

                        By default Filemaker has all the options open to any user, I am used to users only having the power I build and deploy to them.

                   Has to do with

                   

                        The multi-field indexes were all batched with no user interaction, or user visibility.

                   And relationships that link two tables using more than one pair of match fields is a supported feature of FileMaker Pro relationships.

              • 4. Re: Multi Comun Key Alternative
                BenN

                     I am more familiar with a blank nothing as the starting point. You, as the programmer (designer, DBA...) had to add a screen, then a form. Then you had to add fields to it. If you wanted to be able to filter and find, allow find/replace, allow deletion, etc. all that was a choice of the programmer to add those functions. Otherwise the user could do nothing. Under that situation using double or even triple fields as an index was safe. For example an Order::Item::Charge setup could have a single Primary Key of 00123-001-987, each separated by a hyphen. It was guaranteed unique by the programming, full validated before being sent to the DB and the user could never access it to change it. But if a user had access to this field, which Filemaker gives access to all by default, and the user changed the the length, structure, characters or anything else then the program would fail and possibly corrupt the entire DB. I am certainly trying to avoid that.


                     Other systems have more power but certainly take a lot longer to build. Filemaker is a snap to get up and running as it builds a default user interface for you and provides great searching and filtering. It is really just a big change to my way of thinking.

                • 5. Re: Multi Comun Key Alternative
                  philmodjunk
                       

                            Under that situation using double or even triple fields as an index was safe.

                       Yes but uniqueness is not the only characteristic of an ideal primary key. Such compound keys are quite possible in FileMaker and it's quite easy to keep them from being modified in any way by the user. I just don't see that as a desirable design for any primary key in any related database. (not just FileMaker) It's a needless complication to what should be as simple and "bullet proof" a design element as possible.

                       The ideal primary key is:

                       unique

                       never ever changed once assigned to a record

                       Devoid of any "encoded meaning".