Should I include another column here to keep track of cost + tax or not?

Question

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?

Answer 1

This is a legal question. The database schema should mimic what the laws mandate.

Since the tax_rate can change all too often, you really should include the rounded value of tax in the table.

On the other hand, total_cost is necessarily equal to subtotal of cost and tax, you should not include it. A general rule in schema design is not to include any redundant data; that includes readily computable values.

Another thing to think about is "when" the tax is computed. In some types of stores, the tax must be calculated from the subtotal. In other types of stores, the laws may require computing the tax on each item (or multiple of items).

Perhaps you need a boolean flag called taxable.

Some laws say that some items are taxed and some are not. For example, in California, food generally does not incur sales tax. But a grocery store often sells non-food items, so there are two subtotals: "taxable" and "non-taxable". To further confuse things there was, briefly, a "junk food tax" on various 'foods'.

Then comes "VAT" -- which seems to be reverse-computed from the total that the customer pays.