6 Replies Latest reply on Apr 16, 2013 7:56 AM by LaRetta

    New table or additional fields in existing table?




      I'm wondering how FM12 manages its memory for empty fields in a record. I have some data (test data) that is rarely populated but is only related by a 1-1 relationship to my main entity (activity) which has its own table. The question is is it efficient to create these fields in the main table or would it be better to create a separate table for my rarely populated fields and only create a record when the data is populated?


      Thanks for any advice.



        • 1. Re: New table or additional fields in existing table?

          Zoe -


          FileMaker's client-server model is "record-centric", which means when a client requests a record, it gets the entire record from the server - all fields, regardless of whether they're shown on the current layout (with some notable exceptions). Further, it creates a local cache on each client that retains the current contents of each record to keep each client updated on the current values so that client "knows" what changes have been made by other users. Hence, as a general rule, in a situation such as you describe, it's often better to separate out rarely used fields into a separate table to conserve memory and bandwidth.


          However, the presence of 1-to-1 relationships raises yellow flags with regard to the data normalization in the system. (Not red flags, just yellow ones.)    I have built systems such as the one you're describing on purpose because the related records were part of a workflow that the parent records weren't, for example. But often, a 1-to-1 relationship is a marker of a problem in the data model. Not having seen your actual database, I can't say that it is a problem, only that it may be.





          1 of 1 people found this helpful
          • 2. Re: New table or additional fields in existing table?

            Thanks Mike - that is useful information and my situation sounds similar to yours - it is for managing a workflow and I guess I'm being lazy by trying to fit it all into the main table.


            I presume there is a minimum amount of space reserved for each data type whether full or empty. How much is it for each? I have text, number and date fields that are rarely populated.



            • 3. Re: New table or additional fields in existing table?

              Short answer: I don't know.   


              Longer answer: The minimum isn't likely to matter much. What's more likely to matter is largish blocks of text that have to be pushed over the wire.


              Again, this is more likely a data modeling issue than an issue of efficiency or performance per se.



              • 4. Re: New table or additional fields in existing table?

                Hi Zoe. :-)


                Mike provided a great response.  Sometimes 1:1 is very valuable also simply to keep the field definitions smaller and easier to work in.  For example, a government registration form or CPMS can ask singular questions which have only one answer and is only asked once to register so they logically would fit  in the same table as a person but since there are 280 such fields, really should be separated into 1:1 table.


                It is far more important to keep a system fast meaning rarely-used fields and particularly notes or large text blocks exist as 1:1 related so they are not fetched until and unless needed.  Of course if needed for calculations or relationships then they probably should reside in main table so the calcs can be stored.


                It sounds like, in your example, that the separate table is more logical. 

                • 5. Re: New table or additional fields in existing table?

                  LaRetta is correct. I also want to point out that "rarely-used" in this context can mean more than "only rarely populated". It can also mean "only rarely updated", such as LaRetta's example of a field that's only entered once, at the start of a workflow, and never updated again. The reason is FileMaker's caching behavior I mentioned earlier.


                  When the user cache updates, if any field on a record has been updated, the entire record will download to each client. So, the fewer fields you have per record, the faster it will download (sorta logical). So if I separate out my never- or rarely-updated fields from my frequently-updated fields, it means that the records in the infrequently-updated table won't have to be downloaded because, well, they aren't being updated. Cache refreshes will happen faster and the user experience will be improved.


                  Of course, none of this means we should abandon good data modeling practices; it's a consideration for performance optimization, especially over slow networks. Theoretically, in a properly normalized database, you should already have a low field count in your tables and this should not be a problem. But it's a good thing to keep in the back of your mind, especially since FileMaker is moving into the mobile world and the sloooooooooooow cell network.



                  • 6. Re: New table or additional fields in existing table?

                    Here is how I would consider:


                    1.  Same number of fields whether separate table or in main table

                    2.  Test data would be fetched when main data is served.  Does it need to be?

                    3.  Secondary table and relationship would use negligible resources.

                    4.  Separate table would ease field-definition clutter and make import/export mapping easier.

                    5.  Those test fields would exist in every main record .. Would it always be necessary to have same number of test records as main records?  If not then separate table would be less expensive.

                    6.  Fields needed for relationship or calculations should probably reside in main.