Appendix A — Calculated Attributes

The dilemma

When designing a database, you come across attributes whose value you can calculate from other fields. Think of:

• Subtotal = Quantity × Price
• TotalAmount = Sum of all subtotals
• Age = Current date - Date of birth

The question is whether you should store these calculated values in the database or not. There is no simple answer. It depends on the type of data and on the situation.

According to strict normalization rules, you should not store calculated fields in a database, since you can always have the value calculated when you need it.

But practice is different

In practice, there can be good reasons to store calculated values after all. What are the problems?

1. Historical prices (the most important reason!)
   Suppose a laptop costs $799 today, and a customer orders and pays for 1 laptop. If the price is raised to $899 a month later, there is a problem when Subtotal is not stored in the database but calculated. For the laptop bought a month earlier, the calculated amount is then Quantity * Price = 1 * 899 = 899. And that is wrong, because the customer paid only $799.

2. Discounts and special offers
   Suppose a product normally costs $50, but the customer gets a 20% discount.

When to store and when NOT to store

Do NOT store when:

• the value changes continuously and can always be calculated directly from current data. An example is an attribute such as Age. It changes every year and can always be calculated from the attribute DateOfBirth: Age = CurrentYear - BirthYear
• the calculation is always based on current data and does not involve money.

DO store when:

• it concerns a financial transaction (order, payment, booking). You must always record what a product cost at the time of the transaction. See the problem with the laptop price increase described above. In the transactions table, you then include the actual sale price as an attribute.
  For financial transactions, you always store what applied AT THAT MOMENT. This is called a “snapshot,” and it protects you against changing prices and rates.
• there are discounts or special prices. After all, the discount or promotion may be over at a later time.
• historical values are important. What was the sales tax rate then? What were the shipping costs then?
• performance is critical. If you have to perform a calculation thousands of times a day, it can be more efficient to store the result.

TipRules of thumb for your design

DO store calculated values when:

• ✅ It involves money (prices, amounts, discounts)
• ✅ It is a transaction or event (order, payment, booking)
• ✅ The underlying values can change (prices, rates)
• ✅ You want to keep a “snapshot of that moment”

Do NOT store calculated values when:

• ❌ The value changes continuously (age, inventory)
• ❌ It can always be calculated from current data
• ❌ There is no financial or historical component
• ❌ The underlying data is permanent