Calculated field vs Complex queries in a database (using JPA)

Question

We have a stock control system which manages the flow of certain items in and out of the business or given location. We have records in the db representing the movement of stock and the inbound and outbound deliveries.

If you were to add up all the inbound deliveries and then the outbound deliveries and subtract one from the other, you have the current stock level. To find the current quantity at a given location you have to include movement records too.

This obviously leads to some pretty complex queries just to get the current stock level. Worse than this, we're using JPA which can't handle all this in QL so we end up having to use native queries or query large result sets and process the results in the application.

Clearly, adding a quantity field would make life much easier, but is technically redundant, and would have to be updated for every stock movement.

Which way would you go on this?

Answer 1

I assume that you have pretty much normalised the database structure so you have no redundant columns and hence are questioning the need to add seemingly redundant data back into the design.

I would be leaning towards add the quantity column provided that you can ensure that the data in the column can be kept consistent whenever local stock levels change, be it by triggers in the database or additional code in your application that takes care of this. Spec out the work needed to ensure consistency and if it still looks like its impact is less than running the potentially large queries that you are considering as an alternative and you can implement them with a similar amount of effort(and potentially gain a performance benefit as well), I'd be tempted to add the column and the necessary code to ensure its consistency.

The danger I can see here (and that's why I said I'm leaning towards adding the column and didn't say "Nike") is that the effort required to ensure data consistency might be higher than people think it is and you end up with a half-baked implementation and data consistency issues three months down the line.

Answer 2

A couple ideas to mull over:

Have a StockLevel entity?

Obviously, I don't know what your current domain model / tables look like, but perhaps you could have a StockLevel entity:

// annotations not shown
public class StockLevel {
    private Item item;  // unidirectional one-to-one?
    private int inbound;
    private int outbound;
    public int getInbound ...
    public int getOutbound ...
    public int getLevel ... // derived from other two
    // setters
}

You would need to make sure that the StockLevel entity is always updated in the same transaction as the stock movements? These StockLevel's could become a source of contention however.

Use DB-specific solution

You could consider using DB triggers to prevent the need to continually recalculate the same number.

You could also use a stored procedure to hide how the DB calculates the stock level from its caller. So, you could start off moving the query into a SP, and then investigate other approaches while maintaining the same application code.

Answer 3

This is a good candidate for a calculated column. You get the benefits of a quantity column without the worries of having to keep it in sync with the other fields.