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 totalCost
be 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 units
costPerUnit
and tax
and leave the rest up to doing some intra-query calculations, and under what conditions would this be preferable?
view
? But I was curious, as I've come across this a lot in the database at work, times where it's stored and times when it's calculated, and I was just thinking more generally when is an appropriate use case for each? – bjk116 Aug 8 '19 at 19:14