6 Replies Latest reply on Nov 26, 2012 2:11 PM by timwhisenant

    Structure Approach - Pros and Cons

    jormond

      I am working on a solution that involves some Construction Cost Codes and reporting. The solution itself isn't really all that complex ( think: Microsoft Money replacement )...but I could use some more thoughts before I sketch out the structure and start building.

       

      For ease of explanation, I'll start with the reporting requirements.

       

      Reporting:

      • ALL expenses are categorized to the construction cost code for each aspect of the project.
      • Totals by date range ( usually monthly ) required for:
        • Cost Category ( a third level category, a sub-sub-category if you will )
        • Sub-category ( 2nd level in the category structure )
        • Category ( highest level classification )
        • See below for a sample

       

      Category Sample ( highly abridged ):

      1. Land
        1. Purchase price
        2. Taxes
          1. Property taxes
          2. Sales Tax
      2. Permits
        1. Studies and Engineering
          1. Land Survey
          2. Traffic Study
          3. Phase I Environmental Study
        2. Permits
          1. Land use
          2. Off-site Permits
          3. Lighting
          4. Landscape
      3. Building
        1. Utilities
          1. Utility Setup
          2. Utility Bills
        2. Doors, Windows
          1. Frames
          2. Exterior Doors
          3. Interior Doors
        3. Insulation
          1. Rigid Insulation
          2. Foam Insulation
          3. Blown Insulation

       

      Data Entry

      • Expenses can be coded to any of the Cost Categories, OR to a more general Sub-category OR at the top level Category.
        • 90% of expenses will be coded to the Cost Categories, but we need to be able to code to any level.
        • I have NO control over the cost codes, and they can't be changed.

       

       

      Data Structure

      • If I didn't need to post to the higher level categories directly, I would normally just create 3 separate tables, and relate them to each other.
      • I might even just use a single table and a self-join or two to create the relation...since this is a very strict 1-to-many relationship. Never the possibility of a Many-to-Many here.

       

      Questions

      • What are the pros and cons you have run into using either approach?
      • How have you approached reporting when you need to roll together the various levels, and also post to them all?
        • Example: I need to get totals for "Blown Insulation", which rolls into the total for "Insulation" sub-category, which rolls into the "Building" main category.
        • Expense will be posted directly "Blown Insulation", some to "Insulation" sub-category, and some other expenses to the main Category "Building".
        • 1. Re: Structure Approach - Pros and Cons
          pfroelicher

          Dear Joshua,

          I will try to answer, although I did not fully comprehend, what you are trying to accomplish.

          I assume that you want to categorize a list of payments into categories.

          I would to that with value lists and two or thee fields. Then I would use the "filtered valuelist" trick and droplist so that the user only sees the sub-categoris to put into the second  or third field and only inputs valid information.

          Example: pay0001 for xxx,xxUSD has category "windows/doors", and in the second category field the user chooses from a dropdownlist from the items that are available in this category, for example.

          Up to now, this would be a flat db approach.

           

          yours

          Pierre

          • 2. Re: Structure Approach - Pros and Cons
            jormond

            The selection of the categories I don't have a problem with.  I have lots of options.

             

            I'm actually looking for  thoughts on experience with reporting based on:

            • Category, Sub-category, Sub-sub-category where the user can assign the expense to any level of categorization, but the lower levels still roll up into the upper levels.
            • Data structure is either 3 separate, related tables or a single table with a self-relationship.

             

            There are probably many considerations that I haven't thought of yet, and I was hoping to lean on the experience of the Developers here.

            • 3. Re: Structure Approach - Pros and Cons
              pfroelicher

              Joshua,

              why would you need 3 tables? Because you have 3 levels of categories?

              I stil think that this is accomplished with a flat file and every payment has 3 category fields.

               

              If however you would like to do something like a payment, which has sub-payments you have two options.

              Either a self-relationsship:

              A master-payment can have children. Each are based on the same "payments" table but the child has a field for a foreign key of the father.

               

              Advantage: only one table, Disadvantage: You have to omit cirular loops, where masters with children become children of themselves.( If you are interested I can send you an article from Jonathan Stark which is excelent it is called " recursive data structures") .You have to write some code to get the reporting right.

               

              Or difrerent lists, one for masters or father payments and one for children.

              Advantage: easy reporting, Disadvantage: not very elegant. Various tables.

               

              Yours

              Pierre

              • 4. Re: Structure Approach - Pros and Cons
                timwhisenant

                Hi Joshua,

                 

                 

                 

                You have two conflicting statements.

                 

                *     “ALL expenses are categorized to the construction cost code for each aspect of the project.”

                 

                And

                 

                *     “Expenses can be coded to any of the Cost Categories, OR to a more general Sub-category OR at the top level Category.”

                 

                I would allow posting only to cost code level and the standard sub-summary reporting would fulfill your needs.

                 

                As an accountant, I would consider it bad form to allow posting to grouping headers, i.e. categories or sub-categories. Especially, if the structure is permanently set as it seems from, “ I have NO control over the cost codes, and they can't be changed.”

                 

                If only cost codes allow posting, then I would use a single table with self-joins as you mentioned ( 1 to many ), additional tables seem to me to be unneeded overhead.

                 

                 

                 

                My 2cents,

                 

                Tim

                1 of 1 people found this helpful
                • 5. Re: Structure Approach - Pros and Cons
                  jormond

                  timwhisenant wrote:

                   

                  I would allow posting only to cost code level and the standard sub-summary reporting would fulfill your needs.

                   

                  As an accountant, I would consider it bad form to allow posting to grouping headers, i.e. categories or sub-categories. Especially, if the structure is permanently set as it seems from, “ I have NO control over the cost codes, and they can't be changed.”

                   

                   

                  I agree with you Tim.  Except, the "no control" filters into the workflow...and it's part of the business rules that an expense can be categorized into those higher levels.  I also come from the accounting world, and while I wouldn't normally work it this way, it's what I have to work with.

                   

                   

                  You have two conflicting statements.

                   

                  *     “ALL expenses are categorized to the construction cost code for each aspect of the project.”

                   

                  And

                   

                  *     “Expenses can be coded to any of the Cost Categories, OR to a more general Sub-category OR at the top level Category.”

                   

                   

                  Yes, that was poor wording on my part....sorry.  This is a more accurate statement:

                  • All expenses are categorized for each aspect of the project.
                  • Expenses can be coded to any of the Cost Categories, OR to a more general Sub-category OR at the top level Category.

                   

                  At the moment the plan is to use a single table, and any needed self-joins to create the relationships between the various categories. Then the reporting itself will be a Virtual List / Data Warehouse hybrid. And expense will only need a single category field ( 1 click instead of 3 ).

                   

                  This is a first for me...encountering Categories that are themselves a sum of everything under it and hold transactions too.  I have also considered adding a Cost Category for my own internal use...that mimics the group.  But I'm undecided if that's worth the extra work, since I will be gathering the reports through a Virtual List anyway.

                  • 6. Re: Structure Approach - Pros and Cons
                    timwhisenant

                    Hi Joshua,

                     

                    Since this is true: “and it's part of the business rules that an expense can be categorized into those higher levels.”

                     

                    The only way that makes sense is:  “gathering the reports through a Virtual List anyway”

                     

                     

                     

                    I also thought of each grouping to carry an “general” cost code for each grouping level which always sorts to the top of each relative grouping.

                     

                    You might could use a sub-summary with a calculated header which turns invisible for the invisible general cost code.

                     

                     

                     

                    HTH,

                     

                    Tim

                    1 of 1 people found this helpful