Edit Question - KnowledgeHub

Title Body

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? 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: 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 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? 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: 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 < Upvote.date GROUP BY Upvote.id ) AS UpvoteWeight ON Question.id = UpvoteWeight.Question_id GROUP BY User.id 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)?

Tags (comma-separated) Cancel