My database project tracks monthly progress invoices and payments for construction projects, "jobs".
A job has dozens of items, or tasks, to bill for each month. An item has a total dollar value, if x% of an item is completed in a month we can bill for x% of the item's value for that month.
My question: would it be best practice to store progress on an item as % (float) and just calculate dollar values in the invoice (reports), OR since we're dealing with currency and access is so good with currency, store my progress as currency dollar values?
I had assumed just working with currency data types entirely would minimize potential for issues with rounding and that kind of thing, but now that I thought about it for a moment I'm wondering if my concerns we're even an issue. The current spreadsheet that's actually used to track these values, the spreadsheet(s) I'm trying to replace with db, tracks them as percentages...
I realize as a general rule you don't store calculated values in a field, but the circumstances feel different here.... I suppose it's not a calculated value that would ever change once it's recorded (or at least not supposed to).
I can think of a few pros and cons for storing progress value as either pct or currency. But I'd like to know if anyone's dealt with a similar situation and has thoughts?