How do I create a 'feed' to subscribers/followers of an item in PHP/MySQL?

We have a group of 'Stores' which also have 'Products' listed in each of them.

As part of an update/notification system, we allow users to 'follow' stores and 'watch' products in stores. If a store posts an update, or gets modified; or if a product changes price, or is modified, etc - we want the notification to appear in the feed of the followers/watchers.

I do not want to have to insert a unique record into a notifications table, because some of these stores/products could have a large number of followers/watchers. We are trying to make sure this scales properly.

On the same note, if a user 'unwatches' or 'unfollows' a product or store respectively, we want those notifications to disappear from that feed of that user.

The last desirable aspect is to have the ability for it to mark a notification as 'viewed' or 'read' when a user has seen it, so we can put a notification bubble in their nav bar.

My difficulty is really coming up with the schema to support this, without the need for excessive inserts into the database table when a new event is posted to a feed.

Can anyone suggest a simple database schema that I could use to support this architecture?

Solution

I don't know the proper way to mock up a database design in plain text, but hopefully my example is understandable. Would the following work?

Scheme #1

table: users fields: user_id

table: stores fields: store_id, last_update

table: products fields: product_id, last_update

table: subscriptions fields: user_id, id [store_id|product_id], active

table: feed fields: row_id, user_id, id, notified_date, viewed_date, active

mock up a few examples

subscriptions:

doej, store1, true
doej, product1, true
doej, product2, true
...

when user un-subscribes

doej, store1, false

feed:

create feed row when store updates, send notification

1, doej, store1, 2015-01-01, null

when user views update the row

1, doej, store1, 2015-01-01, 2015-01-03

Of course the user's view of the 'feed' would probably involve a join of subscription and feed tables and a check that subscription active is true

Scheme #2

The trouble with scheme #1 seems to be the feed table will be larger than you want, because it will be the product of all subscribed users and all updates. Instead of a feed of all possible updates, maybe use a combination of a date threshold and a log of viewed updates.

table: users fields: user_id

table: stores fields: store_id, last_update

table: products fields: product_id, last_update

table: subscriptions fields: user_id, id, active

table: updates fields: post_id, id [store_id|product_id], date, description

table: viewed fields: post_id, user_id, date

product update post

post1, product1, 2015-12-03, 'new and improved'

user is subscribed

doej, product1, true

if update post is within a date threshold, and user is subscribed to product, and no record on the viewed table, display post for user

user views the post within the date threshold (for example within 3 months) so update the viewed table

post1, doej, 2015-12-07

subsequent site visits don't display post to user, it was already viewed

With this scheme there will only be one record for the store/product updates, and the only records for the user 'feed' activity are actual views. Everything else will roll off the 'feed' view when items are older than the date threshold.

So I think what I am aiming for is record only the active events: product updates and user views. Notifying a user of an update is passive - in the sense the user might not view it - so don't record it in a feed. Handle the passive events with a date threshold.