2

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. enter image description here

How would you do it?

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

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

Improve this question
2

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”.)

Improve this answer
  • Hi Jason, thanks for the inputs. First I'm calling reviewproduct and not review because I also have localreview that represents reviews to the local itself. That seems a good idea of the timestamps, I was thinking that I would need a table to keep track of the new ids, but that seems a good idea. Perharps I will also do a many-to-many relation between user and local, so that I keep track of new products added to the local – pwerspire Jan 16 '17 at 10:29
  • Use UTC timestamps to avoid problems with daylight savings, distributed servers and such like. – Michael Green Jan 16 '17 at 11:02
  • @pwerspire OK, I'd be inclined to call them localereview and productreview then, to keep it consistent. – jasoncrawford Jan 21 '17 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.