Database for tracking notifications or user activity (e.g. Facebook)

Question

I just read the following posts on the same topic:

Facebook like notification tracking (database design) and Database design for storing notifications to users

Some solutions were proposed, but not quite what I needed, and how it should be for Facebook's notification system.

In a notification, we often have links pointing to the user who took some action, a link to a message or video that he commented on, a link to anything, and we have several links in one notification.

notification ----------------- id (pk) userid notification_type notification_text timestamp last_read 

With this table structure, we can show all notifications for one user, and this is a pretty solid solution. But in this case, we can only display a text notification. We cannot just refer to a user or wall.

I am trying to find a solution to this problem. One of them is to store BB codes in the notification_text property, but then you need to write a BB parser for both web applications and mobile applications. Another will be the creation of another table, which is obtained from this notification table with an identifier for the objects that we need. Example:

 PostCommentNotification : Notification ---------------------------------------- id userId (user who commented on a wall post) postId (post where comment was made) 

Now we can write a template to display the notification (now we no longer need a text property in the notification table), and then process its display. I am not satisfied with this decision either because the number of tables obtained from the notification table may be large (for each type of notification).

I'm looking for ideas! :)

Answer 1

Unfortunately, there are not many answers here. I had the same problem and did not find a good solution. Database inheritance is always complicated and too complex. So I ended up with a simple solution: storing an array of key resource values ​​wrapped in JSON in the "data" column.

Something like that:

 notification ----------------- id (pk) userid notification_type notification_data timestamp last_read 

For example, for notification of comments you save

 [{"author_id": "1234", "comment_id":"1234"}] 

Then you use the notification_type function to properly format your data on the client side.

It is impossible to see the flaws, since we only need the identifier of the resources on the client to create URLs or intentions, the presence of atomic fields for indexes is useless.

Hope this helps.