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?

  • In this case I don't think there is an advantage to storing the calculated value. This is such a simple calculation that just adding it in a SELECT statement should produce virtually no speed loss. If it was a super-complex calculation or there were millions of records it might be helpful to store the calcualted value, but I think you should be fine without it. – raphael75 Aug 8 '19 at 19:08
  • Sure and I realize too this might be the point of a 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

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.

  • I do have a taxable boolean flag column. However, I was unaware about the legal issue. The (small) company I work at is trying to move all their stuff out of real accounting software into our inhouse application and then strictly use that going forward when its ready. What term can I search to learn more about the legal side of this and what stuff I legally need to store in my database for my state? – bjk116 Aug 9 '19 at 13:53
  • 1
    @bjk116 - The state may not talk specifically about "databases" (they possibly still have mechanical calculators on their desks), but they should have a brochure on the requirements of calculating and reporting sales tax. That is the "legal" issue I was referring to. – Rick James Aug 9 '19 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.