I am making a two database tables tho add a sales order line item functionality to my application. On the second table, which lets call
salesorderlineitems, I have the following schema
CREATE TABLE salesorderlineitems ( other columns here, units INT(11), costPerUnit DECIMAL(11,2), totalCost DECIMAL(11,2), tax DECIMAL(11,2) );
Now I realized after some developing that besides
totalCost being sort of a misnomer since it's only
costPerUnit * units, and not including the
totalCost. That made me think, should I change the name of
totalCost to say
totalUnitsCost and have
costPerUnit*units + tax.
That made me think well I can always just calculate that manually by doing
totalUnitcost + tax, so why bother saving it. But then I started thinking of well that's also true of
totalUnitsCost since it's definition is just multiplication of two other columns.
I know my use case here is pretty simple but I'm sure this concept has popped up in many peoples databases. Is it worth it to have a column that is just a calculation between two other columns, and under what conditions is it so? Also, how would I document this if say I choose to go the other route and and only keep track of
tax and leave the rest up to doing some intra-query calculations, and under what conditions would this be preferable?