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?
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.