14 Replies Latest reply on Apr 25, 2017 6:52 AM by TomHays

    Number of fields

    alice07

      I have two tables containing only global values. The first is for internal use, the second contains all user fields used in any contest.

      Both tables have then only one tecord but the number of field is growing, reaching 300/400 fields each.

       

      Is that either a problem or a bottleneck for filemaker to handle tables with so many fields ?

       

      Should I separate them in more smaller tables ?

       

      thanks.

        • 1. Re: Number of fields
          philmodjunk

          You have created a very unweildy design. It won't break by hitting a built in limit anytime soon, but it will become increasingly difficult to work with, plus the need to keep making design changes to add more fields when a simple data entry process could add more records.

          • 2. Re: Number of fields
            coherentkris

            Filemaker limits fields/columns to 256 million over the lifetime of the file.

            As Phil suggested having 400 fields in a table is probably not the best design.

            You should read up on Normalization

            Database normalization - Wikipedia

            • 3. Re: Number of fields
              alice07

              philmodjunk

              I cannot really see what you think or better what could be a more efficient solution according to you.

              database is (imho) well normalizaed and these two table are a kind of global fields repository.

              • 4. Re: Number of fields
                philmodjunk

                Both tables have then only one tecord but the number of field is growing, reaching 300/400 fields each.

                The text in bold indicates that this is not the case. You should never have only one record in the table and 300/400 fields in that one record.

                • 5. Re: Number of fields
                  TomHays

                  What you describe shouldn't be a problem to FileMaker.

                   

                  (I interpret that you have a properly normalized database with many tables.  Two of these tables are reserved to hold globally stored fields exclusively.)

                   

                  Keeping track of and editing a long list of fields in one table can become unwieldy for the human interface.

                   

                  For your sanity and for any others who will maintain this, you may want to divide up these fields into appropriate general categories and keep them in separate tables.  I would do this only if these can be obviously divided.  Random arrangements into separate global-field-only tables may cause additional confusion.

                   

                  -Tom

                  • 6. Re: Number of fields
                    alice07

                    TomHays

                     

                    Thanks, Your interpretation is correct.

                    I was many times temped to divide fields in separate and categorized table (mostly for my sanity ...), but it will require an hard work with layouts and scripts using those fields.

                     

                    I'm glad to know that it has no impact on performance.

                    I will move fields but unhurriedly

                     

                    • 7. Re: Number of fields
                      philmodjunk

                      I'm glad to know that it has no impact on performance.

                       

                      Well "wide" tables--tables with hundreds of fields, does have a negative impact on performance. Each time you reference even one field of a record, you get all the fields that make up the definition for that record. In a list view of multiple records, wide tables mean that a truly stupendous amount of data has to be served up to the client as the user scrolls through the list.

                      • 8. Re: Number of fields
                        BruceRobertson

                        It is possible we could make the job a lot easier if you would provide a list of your field names.

                        There are a number of ways to do this; including creating a DDR; or providing a clone of your file; or going to manage database; go to tables; and print, and choose PDF as the output.

                        • 9. Re: Number of fields
                          alice07

                          philmodjunk

                           

                          Hi, thanks for your answer.

                          Still not sure to understand: you mean: if I reference one of the global field from table AAA in a record, that record loads the definitions of all the fields contained in that record of table AAA.

                          In this case I'm afraid you are right about performance....

                          • 10. Re: Number of fields
                            alice07

                            BruceRobertson

                             

                            It's really no problem for me to provide a list of fields name from the global field container table, but I don't see what kind of information it could add.

                            • 11. Re: Number of fields
                              BruceRobertson

                              400 fields and nobody could make sense of the field names?

                              • 12. Re: Number of fields
                                alice07

                                BruceRobertson

                                 

                                Related to performance ?

                                • 13. Re: Number of fields
                                  BruceRobertson

                                  Just go ahead and provide the 400 field names.

                                  • 14. Re: Number of fields
                                    TomHays

                                    What philmodjunk is describing is referencing a non-global field in a record.  All fields specific to that record are loaded since records are loaded from the database as a unit.  If a record has an abundance of fields, and you only need one field, you pay the cost of loading that entire record each time you use that field.

                                     

                                    But that does not apply in the situation you describe.

                                    Your record consists entirely of globally stored fields.  These are loaded into memory and kept in memory when the database is first opened.  These values are stored to disk when the database is closed.  It does not matter in which table these globally stored fields are located.  Referencing a globally stored field does not cause the rest of the record to be loaded (the non-global fields) since you are not pulling from a specific record..  (Of course, your record has no non-global fields.)

                                     

                                    -Tom

                                    1 of 1 people found this helpful