AnsweredAssumed Answered

Structure Approach - Pros and Cons

Question asked by jormond Expert on Nov 25, 2012
Latest reply on Nov 26, 2012 by timwhisenant

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".

Outcomes