6 Replies Latest reply on Nov 17, 2015 2:25 PM by etiennedursel

    Data separation model and calculations

    etiennedursel

      Hello

      Some time ago, I decided to split my application in separate "code" and a "data" files.  Now I want to add a calculated field in one of the tables, this calculation aggregates values in a related table. If I am correct this has to be done in the "data" file.


      I realised that I had added that relation in my "code" file and that I had to add it also in the "data" file.


      Is it correct that I need the relationship graph in both files?  Isn't it a risk to have (almost impossible to find ) bugs if there are differences in the two graphs?  Are there any ways to insure consistency between the two graph?


      Regards

      Etienne

        • 1. Re: Data separation model and calculations
          siplus

          Never adopt fashion if you don't understand it.

          • 2. Re: Data separation model and calculations
            dsvail

            Etienne,  at the time you separated your solution into data and code/interface files the relationship graph was the same in both ... but over time all / most work would have been done in the code /interface file as you add functionality...ie. new layouts / scripts for functionality etc....  So,  the two graphs would evolve and be different....that is OK and normal.

             

            In this case you would add a field in the data file and would add whatever logic you need to that file to make it all work, if necessary.... go back to the interface file for all scripting and layout future changes.  Ideally keep the data file simple.

            • 3. Re: Data separation model and calculations
              bigtom

              You never say which file has the tables you wan to add to. If it goes in the code file it is easy. If it goes in the data file it is also easy. Just put it where it needs to be.

              • 4. Re: Data separation model and calculations
                keywords

                Re: "Is it correct that I need the relationship graph in both files?"

                As I understand it, the rule of thumb is in the data file, to limit the relationship graph only to what is essential for data logic—e.g. to join one data table to another.

                For all your operational functionality, you need to create TO's in the interface file for each data table in the external data file.

                 

                Re: "Isn't it a risk to have (almost impossible to find) bugs if there are differences in the two graphs?"

                It should be apparent from the above, that not is it expected that there will be differences between the two files, it is really an essential part of the method.

                 

                Re: "Now I want to add a calculated field in one of the tables"

                This field should be in the table which your data logic says it belongs, in the data file, not the interface file.

                • 5. Re: Data separation model and calculations
                  CICT

                  Etienne

                   

                  There is no need to have identical tables in both files. In fact, the purpose of the separated model is to put the business intelligence in the interface file and keep the data file as simple as possible. In an ideal world there would be no TO joins in the data file at all, just a list of source tables.

                   

                  In terms of cross table calculations, we attempt to use script triggers wherever possible within the interface file to avoid having to add calculations fields, therefore using 'Set Field' as much as possible. However, we appreciate it is not always practical. Usually we would then add the cross table join in the data file(s) and possible enter a prefix to the TO that identifies it as a data file sourced TO. There is no need to replicate this in the interface file unless you have other reasons to do so.

                   

                  Other options are available, although we don't use them, such as creating dedicated calculation tables within the interface file and set up a one-to-one link with the data file TO within the interface file graph. This has some drawbacks in that you're creating records in the interface file, thereby reducing the benefits of the separated interface/data structure (such as upgrade/downgrades). A similar approach can be made at the data level with dedicated calculation tables or files to keep the actual data tables free of calculations.

                   

                  Our view is that we try to keep the data tables as 'pure' as we can, but when it is absolutely necessary to have the cross table calculation field, then just do it in the data file with with all necessary TO links.

                   

                  Regards

                  Andy

                  • 6. Re: Data separation model and calculations
                    etiennedursel

                    Thanks for this response, Andy, it clarifies things a lot.  Having calculations in data tables has always felt to be a ugly departure from theory, but it si so handy...

                     

                    I was planning to use calculations associated with script triggers in reporting, and i will keep cross table calculations in the data file for representing important attributes of the problem at hand.

                     

                    Regards

                    Etienne