Subscription database ER model design

Question

I'm designing a database for a review site.

There are products, and then for each product there can be multiple reviews.

I'm trying to add a subscription system, so that a user can subscribe to reviews and see the new ones when they login to the system.

This is an simple ER diagram with only the part of product and the review, I'm having doubts about how to do the subscription part.

How would you do it?

My thoughts are that first I have to save what the user subscribes to, something like this:

Would you do this in relation to the product or the reviewproduct? Also this is a many-to-many, so I would decompose in an associative entity.

Now my biggest doubt, I need to save the new reviews for the products that the user subscribed, how would you do this in the ER?

Thanks for the help, Br, Tiago

Answer 1

First, if the idea is that users subscribe to products, so that they can hear about new reviews on that product, then you definitely want the (many-to-many) relation to be between user and product, not user and reviewproduct. You're not subscribing to a review, you're subscribing to a product.

Regarding how do you track the new reviews: My first thought would be to keep a timestamp on the user-product subscription entity. When you want to know if there are new reviews for a product, you just query for all reviews created after the timestamp, and then you update the timestamp (or you update it whenever the user views them, or whatever your requirements are exactly).

(Side note: Not sure why you're calling reviews “reviewproduct”. I'd just call it “review”, or if you need to differentiate it from some other kind of review, maybe “productreview”.)