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!)

  • I'm not sure i understand your meaning, the numbers that never change are things i think i do want to save in a table. The things that do change regularly i'd rather not save in a table. Commented Mar 15, 2013 at 11:59
  • 1
    I reread your question. You can store the tax amounts as long as you also store a reference to which tax rate was used. Rates go up and down and you need to show the accountants which transactions used which rates.
    – kevinskio
    Commented Mar 15, 2013 at 12:05
  • Yep, i have that in Place, currently i have a TaxPercentage (tax rate) stored and i calculate it. I'll be implementing an amount table which references all the required tables. I'm just not sure if using a view in order to get the owed amount (so calculated every time based on values in different tables) is the way to go in order to avoid an "OwedAmount" Column in the invoice Table since I believe that's just one more place a mistake can be made. Commented Mar 15, 2013 at 12:10
  • if you're serious about this, please read one of the top data modeling patterns books: Hay or Silverston or Fowler. Commented Mar 15, 2013 at 17:45

2 Answers 2


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.

  • Thanks on the confirmation that my plan to store the actual tax amounts. I've already implemented this in my design by now. Would you say the same is true for the actual deducation of recieved funds? That's the main issue i'm thinking about. Do i store the OweAmount and keep on mutating this amount, or do i store the mutations & initial amount only and then calculate the owedamount? I'm leaning towards the latter due to the fact there will be no possibility of data "corruption" (where for some reason a mutation is alter (made higher) but the owed amount is left unchanged) Commented Mar 15, 2013 at 15:16
  • The reason is that i'm very sure that in the current system these data "corruptions" are present and I simply want to make the system (including the DB design) as idiot proof as i can. Commented Mar 15, 2013 at 15:17
  • I would calculate the owed amount and just store each payment as it comes in. And for anything that is precalculated that could change that you decide to store for performance resons, make sure you havea trigger to keep the data in synch and it is porbably a good idea to havea job that is run nighlty or so to do the updaetes in case anything bypasses the trigger (such as a bulk insert process).
    – HLGEM
    Commented Mar 15, 2013 at 15:24
  • You probably want to do some reading on temporal database design.
    – HLGEM
    Commented Mar 15, 2013 at 15:25
  • Okay, thanks HLGEM so i'm on the right path by thinking i shouldn't store the OwedAmount and just calculate it as i've got the payments stored as they come in. I'll look into the temporal database design some see if this triggers my interest, i'll mark this answer as correct. Commented Mar 15, 2013 at 15:28

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 :-)

  • There are no current performance issues, simply because the old system stores all the values in columns (all over the place basically). Meaning i have values which have been deduced from the Invoice, aswell as the GrossValue of the invoice aswell as the OwedAmount on the invoice, i have this all saved in various places in the old system. Since the programmers made mistakes the values are not 100% reliable (and when dealing with Money i believe they should be!) This is basically a rewrite which should avoid the mistakes from the past. Commented Mar 15, 2013 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.