15

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?

  • 1
    Depends on a number of factors: is your DB partitioned? How many rows/user are you expecting? What size total DB are you expecting (or how many total users)? How many requests per second are you expecting? All of this doesn't have to be accurate, but some rough ideas... – Omer Iqbal Jun 24 '14 at 10:51
  • 10
    +1 This is a classic relational database question. To normalize, or not to normalize? That is the question. Whether t'is nobler in the schema to suffer the slings and arrows of outrageous duplication, or take up triggers, and by employing, end them? – Ross Patterson Jun 24 '14 at 11:14
  • I argue if this is indead a classical Rel. db. question, there should already be an answer on the site, this should be closed as DUP, or we don't have an answer and this should be left open. – mattnz Jun 25 '14 at 4:21
14

How is this normally done or whats the better approach?

The best approach is to try it first without an extra field, measure the performance, and if it really turns out to be too slow, you try to optimize. This could mean to switch to your first approach using an extra field, but you should consider to test other options as well, for example, putting an extra index on the combined fields ("unread", "userID") on your messages.

  • 2
    The best approach is to (go with the simpler method first). General rules are better than specifics, fwiw. (+1 for "test!" though.) – DougM Jun 24 '14 at 12:23
9

The textbook solution acccording to database theory would be to have no values in your database which depend on the values of other data, because these are transitive dependencies. Having fields which are computed values based on other fields is a violation of normalization, because it leads to redundant information.

However, sometimes what the textbook says and what is the most practical method in practice differ. Counting the number of unread messages each pageview could be a quite expensive operation. Caching the number in the user-table would be much better for performance. The cost would be that it is possible for inconsistencies to exist in the database: It might be possible for a message get deleted, added or read without remembering to also update the unread-counter.

  • 4
    The consistency problem is easy to lick with triggers that adjust the counter on INSERT or DELETE. (Or UPDATE, to account for a message's owner changing.). A good DBMS will do the operation and run the triggers in the same transaction, so either all or none of it will happen. – Blrfl Jun 24 '14 at 11:21
4

The potential problem is performance and you don't have a performance problem, yet. There are many things you can do depending on the database of choice to handle this in solution #1: indexing, hardware, caching, etc. This all depends on how frequently the user needs to get a current unread message count. Many of these choices don't require custom coding on the app side, so you can implement them with a code change or very little. Makes it easier to grow with the app.

Once a user connects/logs in, getting the count from the database once is not that bad. Will your app maintain a constantly updating list of messages like email? Getting an unread count from here doesn't require another trip to the database and to get new messages is going to take a db trip anyway.

Taking a trip to the db every time a message is read to flag the IsRead? field is enough without a recalculation of another field.

With solution #2 (keeping a count in a field/on disk), will you need a routine to periodically rebuild/recalc this field when there is a problem? And there are always problems. Are you going to wrap all of this in a transaction? Each time someone sends someone else a message it could fail because it can't update the receiving user's UnreadCount due to a lock of the User table? Or are you going to create a separate table for this field?

  • +1 for mentioning the performance issues with keeping the count fields up-to-date – winkbrace Jun 27 '14 at 7:45
0

The way I would do it is by executing a query every time, i.e. your second approach. Just make sure you add an index in your message table on the column that acts as a foreign key to the users table to improve the performance of your query.

Then as Doc says, measure the performance of this approach and then you will be able to tell whether you need to take a different path.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.