Let's say I have a relational database application and a user object and a message object. Now I want to show the number of unread messages to this user.What's the best way to archive this? Do I introduce a field in the user and count it up if the user receives a message and decrease the count if he reads one? Or do I execute a query every time to compute the number of messages for the user that are flagged as unread?I think the first approach is more complicated and error prone, but will perform better than the second approach.How is this normally done or what's the better approach?
Database design - Store state or compute state every time?
database;performance