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