3

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?

  • What is the column type ? Does it defines if it's IN/OUT or some other thing ? Does the quantity column can be positive and negative or only positive ? – DeadEye Apr 17 '15 at 13:19
  • Yes it defines the transaction if IN/OUT. Quantity is always positive. – Felix Pamittan Apr 17 '15 at 13:41
  • So, what datatype is in type column ? – DeadEye Apr 17 '15 at 13:51
  • Can be BIT or any equivalent data type. – Felix Pamittan Apr 19 '15 at 14:50
  • 1
    @Colin'tHart, it's 10 transaction per product per day. I'm sorry for not clarifying it earlier. – Felix Pamittan Apr 20 '15 at 8:10
4
+50

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.

  • Do you sure that the recommended index (product_id, trasaction_date) will be helpful in case of necessity to get all transactions for specific period? The index data will be distributed and query will not be effective - total transactions amount needed and not per product_id... – Sandr Apr 23 '15 at 19:53
1

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.

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.

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.