title: SQL pattern : compute a sum each query or save and maintain the computed sum data?
body: <p>For a SQL database (PostgreSQL in my case) like:</p>
  <pre><code>A ( 1 to many ) B
  B ( 1 to many ) C
  </code></pre>
  <p>In C and B there is a column size.</p>
  <p>Sometimes I would like to now the global size of A (so the sum of all B/C related to A).</p>
  <p>So it seems to be 2 ways to get the global size of A:</p>
  <ol>
  <li>Get all B related to A + get all C related to all of these B. Then, sum the sizes. It seems to be a huge process for a simple size info.</li>
  <li>Add a size column in A. And each time B or C is added/deleted/updated, update the A size column. This seems betters but need more maintenance and less flexible.</li>
  </ol>
  <p>Is there any other way to do it? May be in native SQL query? Or is the 2nd option the better as it seems to be?</p>
  <p>Example:</p>
  <pre><code>TABLE CONTAINER (id)
  TABLE BOX (id, containerId)
  TABLE ELEM (id, nullable boxId, nullable containerId, size)
  </code></pre>
  <p>Now, let's illustrate with data:</p>
  <pre><code>CONTAINER (1)
  BOX (1, 1)
  ELEM (1, 1, null, 30)
  ELEM (1, 1, null, 100)
  ELEM (1, null, 1, 15)
  </code></pre>
  <p>So the size of container with id = 1 will be: 145</p>