Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As the question states i'm currently internally strugling witha database design question. I work as a software developer (and i tend to do everything database related aswell 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 to redundant having financial values all over the place and it's sometimes a nightmare to actually figure out what the current position of a debtor/invoice is.

In order to not have the same problem i'm currently struggling with the question, do i want to store the positiong of a 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, beeing NettAmount (InvoiceLines has this column, which is the amount before taxes). Bankstatmentlines holds the actual amount recieved on the bankaccount. 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 shouldnt 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 by 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 errorous)

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

share|improve this question
    
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. –  F.B. ten Kate Mar 15 '13 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. –  kevinsky Mar 15 '13 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. –  F.B. ten Kate Mar 15 '13 at 12:10
    
if you're serious about this, please read one of the top data modeling patterns books: Hay or Silverston or Fowler. –  Neil McGuigan Mar 15 '13 at 17:45

2 Answers 2

up vote 3 down vote accepted

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 than must be shown as of teh 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.

share|improve this answer
    
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) –  F.B. ten Kate Mar 15 '13 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. –  F.B. ten Kate Mar 15 '13 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 Mar 15 '13 at 15:24
    
You probably want to do some reading on temporal database design. –  HLGEM Mar 15 '13 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. –  F.B. ten Kate Mar 15 '13 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 :-)

share|improve this answer
    
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. –  F.B. ten Kate Mar 15 '13 at 12:07
    
@F.B.tenKate Thank you for clarifying. –  Serg Mar 15 '13 at 15:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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