Multiple Year Penalty Interest Calculations Architecture Advice Sought 2

[AHJ1]

I'm designing an application, and I'm not sure of the best way to implement one aspect of it. Suggestions will be most appreciated.

Background: I work for the Taxation Department of a state. One of the taxes assessed is a property tax. Sometimes, taxpayers do not pay on time and are subject to a variety of penalties and interest. There are times when penalties and interest can be waived. It is possible to waive any or all of any or all year's interest and penalties.

When a payment is made, it is applied proportionately to all taxes, penalties, and interest due. (Unless a specific interest charge or penalty has been waived.)

A consideration: I desire minimal maintenance.

Suggested approach: A Companies table that is joined to BalancesDue table with fields for each year for balance due for
* Taxes
* Interest
* Penalties (one for each type)

and with fields to indicate waivers in effect for that particular item of interest or penalties.

Question:

1. Does my approach seem right?
2. If so, should this table be built manually, or dynamically, based on the change of year?
3. As each year passes, there will be more years to search through. What's the best way to design / code a loop that does not have to be updated with the addition of a new year. (e.g. as we start 2010, 2011.)

Thanks, in advance, for your insights.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AHJ1]

Thanks, PHV, for your prompt response. Assume that I use the third normal form.

Any thoughts on these questions:

Should this table be built manually, or dynamically, based on the change of year?

As each year passes, there will be more years to search through. What's the best way to design / code a loop that does not have to be updated with the addition of a new year. (e.g. as we start 2010, 2011.)

 

[mmogul]

I would stay away from a separate fields for each year. That will ultimately cause a lot of problems. Unless I misunderstand your application, there should not be anything special about year boundaries.

I would think this could be designed in a similar fashion to any receivables system. Each item (property tax - fall 08, has a due date). You may need to capture when interest begins to accrue for each item (ex: 30 days after due date). That may be in a separate table that applies to all items depending on your rules.

Then I would have a separate table to capture the effective date and ending date for your interest rates - since I would assume that can change over time. (ex: interest might be 6% from Jan 1- Apr 10 and then 6.5% from Apr 10 on).

Then you can dynamically calculate interest based on the amount of time the item is overdue and accounting for partial payments.

From a reporting perspective, even though you are not putting data in "year" buckets, you can still report transactions by year.

When interest or penalties are waived, that should be shown as a credit to the account - this waived data could be in the same file as payment data.

Hope this helps...

 

[AHJ1]

Thank you for your comments. I will update this thread with decisions made, and why.

 

[fneily]

Assume that I use the third normal form." Seems you're new to normalization. Access is a relational database management system. Data is stored in objects called tables. These tables must be constructed by following specific rules. This process to build a table is called normalization (Ted Codd creator). There is a standard five steps but the last two are usually ignored, so it's a three step system. They must be followed in order - first, second, third. No skipping, no shuffling. So all three steps, in order, are used in creating a single table.

So here's what you could have for your database:
Property tax is applied to a piece of land that has an address. This land can be owned by more then one person. So one plat, many owners. And many plats, one owner. This is a many-to-many relationship. Relational databases do not like this. So a junction table is built, as you shall see.
Tables:
tblOwner
OwnerID Primary Key
FirstName
LastName
Phone

tblPlat
PlatID Primary Key
Address
City
State
Zip
Acreage

tblOwnerPlat (Junction Table to connect owners and land)
OPID Primary Key
PlatID
OwnerID

tblTaxTable (suggested by mmogul)
TaxID Primary Key
Rate
startdate
enddate

tblPenalties
PenaltyID Primary Key
Rate
Description
startdate
enddate

tblLateInterest
LIID Primary Key
Rate
Dayslate
startdate
enddate

tblWaiver
WaiverID Primary Key
Description

tblPropertyTax
PTID Primary Key
PlatID
TaxID

tblTransaction
TransID Primary Key
Date
AmountPaid
PlatID
LIID
PenaltyID
WaiverID

You'll noticed with the taxtable, lateinterst, penalty tables they all have startdate and enddate due to state legislatures can change them at will.

Now total tax can be computed from tblPropertyTax being connected to tblTaxTable and tblPlat.

tblTransaction is your payment/waiver tracking table. If people are paying partial payments on different dates or penalties/interest is applied on different dates, then a single plat will have multiple records in this table.

So you can add up the payments for a plat from tblTransactions and then subtract that from the total tax calculated from tblPropertyTax to get a balance. Using queries, of course.

 

[AHJ1]

Thank you for your comprehensive insights. I'm actually developing an application that deals with Personal Property that is centrally assessed.

The purpose of this application was rather limited, but your comments have made me rethink the direction in which I was headed. Thank you.

I will post updates to this thread as I make those decisions. Thanks again.