CODEWITHSUNDEEP
phpsql
adi rohan
adi rohan

Reputation: 806

notification system - read vs write

I have thought about two types of user notification systems .

Both of them has a subscriptions table, where users subscribe to a particular channel.
Below schema is for the table that stores events.

1. Expensive write

When something new happens:

|id| |type| |uid| |read| |created|  
 1    2       2      0     1234

This system , inserts a new row for every user subscribed to a channel when something new happens in that channel.

Drawbacks:

Advantages:

2. Expensive read

When something new happens:

|id| |type| |created|
 1     2     1234

In this system, it inserts only one row for one event i.e it is not specific to a user . The user then reads the table and checks if that event happened is in his subscribed channel and then also compares if the created time is greater than last read time .

Drawbacks:

Advantages:

Which do you think is better for following conditions:

  1. The events can happen (i.e writing) 5 to 10 times in a day.
  2. New events are checked (i.e reading) happens every minute for each user.

Upvotes: 1

Views: 145

Answers (1)

Brad
Brad

Reputation: 163488

I suggest using a combination of your approaches.

Have a table with your messages, and then another table associating the per-user status of those messages with the user. For instance, your messages table might be as simple as:

  • id
  • message (varchar/text)

Your other table might have:

  • id
  • message_id
  • user_id
  • read (boolean)

Then, if you have any other specific information for that user, you can keep track of it outside of the original message. If you update that original message, it is still updated for all users.

Upvotes: 1

Related Questions