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