Search code examples
mysqlsqlnormalizationdenormalizationdenormalized

How to de-normalize mysql database for user notifications?


I think it is necessary to de-normaize database for user notifications. For example, when flagging a post (which should be considered by user), we add a column of flag ENUM('yes', 'no') (or status column). Finding flagged events for a user can be by counting with WHERE clause of user_id='XX' AND flag='yes'.

This normalized structure is fine; but what if we have different types of notifications; e.g. flags for posts, comments, photos ... This means that we need to count several tables when the user is just visiting his profile page. This is more serious for a cross-project like stackexchange, as we get notifications for different sites.

I think de-normalization can help to add notification columns to the user table as

post_flags tinyint(3),
comment_flags tinyint(3),
photo_flags tinyint(3),

In this case, we need to run an additional write query for updating user flag columns on every corresponding actions. For example, when flagging a post: UPDATE users SET post_flags=post_flags+1 WHERE user_id='XX'. My concern is to ensure execution of the latter query to avoid any mismatch between this number and the count of flagged posts; but I think it can be secured by TRANSACTION.

In this way, we get all notification with one query for frequently visited profile pages.

Am I on the right track? or another tricky approach is common for this purpose?


Solution

  • You'd probably be better off with a table of user notifications.

    create table user_notifications (
      user_id integer primary key, -- ? references users, not shown
      post_flags unsigned tinyint(3) not null default 0,
      comment_flags unsigned tinyint(3) not null default 0,
      photo_flags unsigned tinyint(3) not null default 0
    );
    

    A separate, narrower table is both logical and (probably) faster. Unsigned for flags, because negative numbers there make no sense, and MySQL doesn't enforce CHECK constraints.

    As far as normalization goes, user_notifications is in 5NF.