8 Replies Latest reply on Feb 13, 2017 2:22 PM by BruceRobertson

    Normalizing table with too many fields

    nexgen

      I have a table which has too many fields which contains various aspects of the product.

       

      Since the database is hosted in filemaker server and accessed via internet, I think it will cause unnecessary load since I think the whole record is downloaded even if I need just a couple of fileds in certain layout.

       

      I think it will be better to split the tables into multiple tables with relevant data. This will cause one to one relationship. Is it good or bad?

       

      If it's good to do that then is there anyway I can automatically create record in other related table whenever the record is added in the original table?

        • 1. Re: Normalizing table with too many fields
          philmodjunk

          What you describe is a commonly used method for getting a "narrow" table and the reasons you give is exactly why it is done.

           

          There's generally no need to automatically create the related record. If "allow creation" is enabled in the relationship, the needed new record will be created automatically, the first time that you enter data into a field of the related table from a layout based on the parent record. However, if you use a script to create the new record, the script could create new related records at the same time.

          • 2. Re: Normalizing table with too many fields
            nexgen

            I just now created a relationship with allow creation/deletion relationship however, when I create new record in the main table it doesn't automatically create new record in the related table.

            • 3. Re: Normalizing table with too many fields
              philmodjunk

              You are correct. That is exactly in agreement with what I posted.

               

              Let's say you split your table to get Table A --- Table B

               

              WIth "allow creation" specified for Table B.

               

              Then on a layout set up for Table A, you add fields from Table B.

               

              If you create a new record for Table A, The Parent record, no Table B record is created, BUT, if you then enter data into one of the fields from Table B placed on your table A record layout, the related record table is automatically created. Thus the record was not created at the same time as the new parent record, but was created just at the time that you needed that record.

               

              Note that the behavior of your Table A layout is almost exactly the same as if all the fields were in one table.

              • 4. Re: Normalizing table with too many fields
                nexgen

                I just now tested what you said and it works.

                 

                However, there's a problem. I created second table for some calculation fields which is used for another layout. None, of the fields are needed to be entered by the user manually in the second table.

                 

                Should I create a temp field which will automatically populate with some fake data when the user create new record in the main table? That will force the second table to have new record as well as when I delete the record from main table it will automatically delete that record from the second table (which is what I want)

                 

                The layout from second table needs to have all the records from the main table to work properly.

                • 5. Re: Normalizing table with too many fields
                  philmodjunk

                  It's hard to say without knowing more about your set up, but I did note in my first reply, that if you need to add a new related record every time you create a new parent record, you can use a script to create both the new parent record and the new related record. This need not put any "phony data" in any field. It need only create the new record in the related table and put the needed value from the parent record's primary key into the related record's foreign key.

                   

                  So from the parent record layout, you need only use:

                   

                  Set Field [RelatedTableTO::_fkParentTableID ; ParentTableTO::__pkParentTableID ]

                  • 6. Re: Normalizing table with too many fields
                    nexgen

                    Which script trigger should I use for that?

                    • 7. Re: Normalizing table with too many fields
                      philmodjunk

                      I'd use either a button or a custom menu that performs the needed script when "new record" is selected from the records menu. (Custom menus require FileMaker Advanced in order to create and install them into your solution.)

                      • 8. Re: Normalizing table with too many fields
                        BruceRobertson

                        How many fields are you actually talking about, in the original table?

                        Also, far you really haven't talked about normalizing at all.

                        If there are any numbered fields - Customer01, Customer02, Color01, Color02, or repeating fields; then you really should be addressing the normalization question. It would be good to see a list of your actual fields.