Data Modeling question for finanicial system

Hello, my name is Rob I am a systems
developer at a finanicial company. I started working here 2 years ago on a system that was designed by an outside consulting
company. The system is basically a loan servicing system on a Oracle platform. It consists of credits and debits and interest calculations. My question is about the physical design. They created a master transaction table that basically has the debits and credits for each individual account. They also created a transaction detail table that has a foreign key to the master transaction table; this table
contains how the payments are distributed. Example: if an account has a $50 late fee,
$20 audit fee and a payment comes in for
$100 the table will show that $50 of the
payment went towards the late fee, $20 of the payment went towards the audit fee then remaining $30 went towards principle. As you can imagine there is a lot of overhead in maintaining this table especially when corrections to fees and payments comes in and we get a lot of them. It seems
unnecessary for this detail table. All the data that is stored in it can be derived from the business rules. Is this table common practice in financial systems to track how payments are distributed? It seems to me you would only be concerned about the individual balance buckets i.e. Principle balance, fee balance and interest balance. I realize that the complexity of the system would shift over to the reporting side. It would seem safer to derive the data from the reports then to store it in this huge table. We have gotten burned several times do to corrupted data in the detail table.

--Posted from EarthWeb Discussions. http://discussions.earthweb.com Received on Fri Apr 23 1999 - 09:49:28 CDT