Database Design question, calculate or save financial positions

Question

As the question states I'm currently internally strugling with a database design question. I work as a software developer (and I tend to do everything database related as well since we're rather small) for a financial company and we're doing a revamp of our current system.

Our system is starting to become outdated and the biggest issue we have with the current system is the database. My predecessors have made some "challenges" for me in the current design and I'd like to not fall into the same pits in the new design.

Basically we've got things like invoices, adminstration costs and all this fancy stuff.

Added on this we have Creditinvoice, administrational adjustments on payments.

The current system is WAY too redundant having financial values all over the place and it's sometimes a nightmare to actually figure out what is the current position of a debtor/invoice.

In order to not have the same problem I'm currently struggling with the question, do I want to store the position of an invoice somewhere, or do I want to calculate this through a view (indexed maybe; unsure since I've never indexed my views)?

Simply put I have the following tables (it's a slightly simplified example but I think it paints the picture);

• Invoices
• Invoicelines
• TaxPercentages
• BankstatementLines
• Entries (a table that shows what value of a statement is "booked" on the invoice)

Now, InvoiceLines, BankstatementLines & Entries are currently the only tables with a financial value in them, being NettAmount (InvoiceLines has this column, which is the amount before taxes). Bankstatmentlines holds the actual amount recieved on the bank account. Entries hold which amount (could be partial) from the bankstatmentline has been booked on the invoice.

In order to calculate the position I'd have to do the following;

• Calculate the Tax values for the invoice (Adding this to the Nettamount gives me the grossamount)
• Subtract the amount from the entries from the GrossAmount so I end up with an "OwedAmount"

This is somewhat complex, however I'm 100% sure that I will never have a wrong value in my database, since I'm not storing any "GrossAmount" or "OpenAmount".

Personally I'm thinking I should add a "TaxAmounts" Table which simply holds the calculated tax amounts (these numbers never change so calculating them over and over seems redundant). This also goes for the GrossAmount. If I have the TaxAmounts & the NettAmount I know the GrossAmount, which should also NEVER change.

I think doing those two things is the right way to go as those values should NEVER change. If they do something went horribly wrong anyway so recalculating on the fly to fix an error shouldn't be an issue.

My current plan is to calculate the owed amount in a view (most likely indexed on the invoice PK). I wonder if this is the best way to go at this "problem" though? Maybe there is some way of doing this that is more efficient but has the same end result (not having an "OwedAmount" column somewhere that needs to be kept up to date and has a risk of becoming erroneous).

Other information; We'll be hosting this on SQL Server 2012 (currently designing in 2005 but the licenses for 2012 have been ordered and are on their way!)

Answer 1

You want to store the details of the transaction at the time it happens and not calculate at the time you select the record. This is because the information the calculation is based on (such as tax rate ) is subject to change. So yes, store the tax calculation. This is not an issue of performance, it is an issue of temporal data that must be shown as of the date of the action not as it is today. The alternative design is to put dates on everything you use to calulate and then calculate on the fly joining not only on the id but on the date, but this is far easier to make a mistake on and far more likely to be a performance problem.

Answer 2

As I understand, you are going to store some intermediate calculated results along with source data and rely on both in different parts of your system. Personally I do avoid such approach. This is definitely not redundant.

Why do you need to do this? It seemed that somewhere you have performance issues. I think the question should be reformulated in the following way: there is an issue with performance when displaying current position of a debtor/invoice for user on the form; there is a problem with performance while creating monthly report on current position of a debtor/invoice; etc. In such case it is easier to look for solution.

From the other side, creating appropriate view is universal solution in such situation. Maybe it's quite difficult to make them perform well but it could be done in one or another way.

One thing about data which never changes. There is possible trouble which should be take into account - this data may require to be deleted. This is unobvious kind of changing data.

Sorry if I misunderstood your question - too much business information unclear for me :-)