title: Calculated field vs Complex queries in a database (using JPA)
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Clearly, adding a quantity field would make life much easier, but is technically redundant, and would have to be updated for every stock movement.</p>
  
  <p>Which way would you go on this?</p>