Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Store, or generate values?
Greetings,
I did a system awhile ago that maintains information about loans - computes interest, accrued interest, payoff amounts, etc. Ledgers with all information about a loan could be displayed on the screen or in a report. At the time, I decided to only store the following information about activities:
debtor ID
sequence (in case of more than one activity on a date)
effective date
posting date
entry type (payment, credit, addition, etc.)
amount
applicable interest rate (defaults to stored interest rate, but can be changed on entry)
In the debtor record, along w/the Debtor ID and other debtor info, the system maintains, for ease and speed of display:
Current balance (NOT payoff amount, since payoff amount would have to include interest to date)
Current accrued interest balance
The above, of course, needs to be updated as appropriate - transactions are used.
Each time the debtor ledger needs to be displayed, a cursor is created that shows all activity, w/the current payoff amount. The system, therefore, has to process each activity from the initiation of the loan. The system is running on a Novell network and the client is satisfied with the speed, etc. The system has over 5,000 debtors.
I am now looking at a similar type of system (although more complicated - w/late fees, due dates, etc.) and am re-thinking the method I used before. Would it be better to store all ledger fields in the table? or just the activities, as above, and recompute the ledger each time it is displayed/printed. (I can see using an updateable view for ease of processing when something changes in the middle of the date sequence.)
There are a few things different about this new system...monthly statements will be sent out, where all ledgers (statements) will be printed at once. The number of loans, however is roughly 300 (this island has a population of about 75,000, so "probably" the number of loans will not be growing by leaps and bounds).
Any advice, input is appreciated...thanks for your time. J
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement