When to do calculations

Question

Which way is better to store sales information in a database:

1. Store cost for an item and sales price per item
2. Store cost * qty and sales price * qty - so you are storing the totals in the db

Answer 1

Store all single details for each item, as you will usually need them later (for statistical/information purposes or any other reasons). You have to make sure that you always follow the same calculation logic to get the total sales price. So you maybe have to store currencies, discount, price unit, etc. along with these. Here some more points to consider:

• If you want to output the single item price later, you need the single item data.

• Don't save total price along with single item data, as you will have to always hold both in sync. You'll get puzzled some month/years later, about which of both to use for your calculations.

• Aggregate your data if you are using it in a data warehouse and don't need the details.