Running Total: To store or compute

Say I have a product table with the following columns:

product
--------
product_id
name

And I have another table to record the transaction (IN/OUT) for each product

product_transaction
--------------------
transaction_id
transaction_date
product_id
type
quantity

I want to have a page that lists that products with its details including the current quantity. At the same time, I also want a page that lists all transactions within a day (or a range of dates) that also include the running total for the current transaction.

My question is, shall I store the running total in the product_transaction table and the current quantity in the product table. Or is it quick enough to just compute them on the fly?

Like many things, the answer to this question is "it depends". In this case, what it depends on is how many transactions each product will get.

With low to moderate volumes of transactions, it will be very fast to compute the running total on the fly and you don't have to write lots of code to compute, store and maintain the totals.

Index your table!

-
Make sure you have an index on (or starting with) production_transaction.product_id so that you can quickly retrieve all transactions for a given product.

-
An index on the combination of product_id, transaction_date and a query specifying product_id and a date range (or >= today) will give you a day's transactions for a given product, and with good performance.

Test!

Be sure to test this in your Test / Q&A environment! Generate representative transaction loads, but also much higher loads to see how it will perform in the expected and unexpected cases.

Even at high transaction levels, you will have to weigh up the performance associated with updating and storing the running totals vs how often they are queried.

StackExchange Database Administrators Q#98136, answer score: 4