3 Replies Latest reply on Oct 4, 2016 6:56 AM by RolandGomez

    Database Design

    RolandGomez

      Hello all,

           I need to pick your brains a bit. If I had to rank myself...meh, upper level novice. So here's my inquiry (this involves a Property Tax Consulting Service) and is there anyway around this. I have had to create the following:

           A) Client

                B) Client owns property(or properties)

                     Each property has an assessed value and it is our job to reduce that value...so the also have a reduced value. Here's the kicker, these values change from year to year. So this is a small example of what is in my tables consist of the following

       

                     2013_Assessed Value     2013_Reduction value

                     2014_Assessed Value     2014_Reduction Value

                     2015_Assessed Value     2015_Reduction Value

                     2016_Assessed Value     2016_Reduction Value

       

          So the problem with it is that every year these fields need to be created and it's important to have year to year information because we utilize the previous years to track trends. Currently I have 338 fields and this next month I will create 2017 through 2019. So you can see how this database will continue to grow.  Is there anyway to get around the creating of new fields for every single year???

       

      Just a shot in the dark and hoping you guys can throw me some ideas. Thanks for any help in advance!

       

      -Roland

        • 1. Re: Database Design
          David Moyer

          Hi,

          just to put it simply, you don't need new fields, you need a new table for dates.  This new table, say ValueDates, would have a few fields ... PropertyID, ValueDate and ValueType (Assessed or Reduction).  You would relate this table to your main table via the PropertyID.  And, again simply, you could put a global field in your main table that you would populate with your ValueType keyword and relate it along with the PropertyID to retrieve those specific dates from the new ValueDates table..

          1 of 1 people found this helpful
          • 2. Re: Database Design
            keywords

            Here is a simple, more or less accurate, pair of fields from an inventory.

            AgeField contains the following calc:

            Let ( [

            purchase = <table::Date_purchase> ;

            disposal = <table::Date_disposal> ;

            now = Get ( CurrentDate ) ;

            totalDays = If( disposal ; disposal - purchase ; now - purchase ) ;

            age = Round( totalDays / 365 ; 2 )

            ] ;

            age )

            WDV (written down value) field uses a recursive custom function to deliver current WDV. The CF has three parameters—Value (from purchaseCost field), DepreciationRate and LifeYears (from AgeField)—with the following calc:

            If ( LifeYears > 1 ;

            fn_WrittenDownValue_FINAL ( Value - ( Value * DepreciationRate ) ; DepreciationRate ; LifeYears - 1 ) ; Value )

            // This function calculates a list of figures starting with the initial Value, each reduced by the DepreciationRate cf the one before it, for each year of its LifeYears, but delivers only the final value in the list.

            1 of 1 people found this helpful
            • 3. Re: Database Design
              RolandGomez

              Well this just turned everything upside down for me. Your suggestions make much more sense and are much less cumbersome than what I have originally created. I appreciate it!

               

              Best regards,

              -Roland