4

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?

Felix Pamittan
  • 285
  • 1
  • 3
  • 12

3 Answers3

4

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.

Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
2

Actually, in case of correct indexes existence, you will receive the data calculated in the relatively small time. In this case you don't need to perform the data manipulations relevant for storage of running sums and so on. The indexes will increase the insert time, but influence will be very small (almost slight). For example, if the clustered index (not need to be the primary key) on the product_transaction will be created (on the transaction_date field) you will very quickly receive the screen of the transactions for needed date range. Additional index (on the same table, but on filed product_id with including the quantity column) will help to return quickly all quantities for the needed product's.

Sandr
  • 276
  • 1
  • 2
  • 5
0

The downside of storing the running total and the current quantity is storage and potential locking, as oppose to mostly faster response times when retrieving the data. You should weigh those factors and decide what's more expensive for you. In most cases it will be more beneficial to store them for better response times when retrieving the data, but there are cases where it's not the case.

Matan Yungman
  • 798
  • 4
  • 7