title: In a database, when should you store derived data?
body: <p>My question is about denormalization. In a database, when should you store derived data in its own column, rather than calculating it every time you need it?</p>
  
  <p>For example, say you have Users who get Upvotes for their Questions. You display a User's reputation on their profile. When a User is Upvoted, should you increment their reputation, or should you calculate it when you retrieve their profile:</p>
  
  <pre><code>SELECT User.id, COUNT(*) AS reputation FROM User
  LEFT JOIN Question
    ON Question.User_id = User.id
  LEFT JOIN Upvote
    ON Upvote.Question_id = Question.id
  GROUP BY User.id
  </code></pre>
  
  <p>How processor intensive does the query to get a User's reputation have to be before it would be worthwhile to keep track of it incrementally with its own column?</p>
  
  <p>To continue our example, suppose an Upvote has a weight that depends on how many Upvotes (not how much reputation) the User who cast it has. The query to retrieve their reputation suddenly explodes:</p>
  
  <pre><code>SELECT
    User.id AS User_id,
    SUM(UpvoteWeight.weight) AS reputation
  FROM User
  LEFT JOIN Question
    ON User.id = Question.User_id
  LEFT JOIN (
    SELECT
      Upvote.Question_id,
      COUNT(Upvote2.id)+1 AS weight
    FROM Upvote
    LEFT JOIN User
      ON Upvote.User_id = User.id
    LEFT JOIN Question
      ON User.id = Question.User_id
    LEFT JOIN Upvote AS Upvote2
      ON
        Question.id = Upvote2.Question_id
        AND Upvote2.date &lt; Upvote.date
    GROUP BY Upvote.id
  ) AS UpvoteWeight ON Question.id = UpvoteWeight.Question_id
  GROUP BY User.id
  </code></pre>
  
  <p>This is far out of proportion with the difficulty of an incremental solution. When would normalization be worth it, and when do the benefits of normalization lose to the benefits of denormalization (in this case query difficulty and/or performance)?</p>