0

enter image description here

Customers have the option to not provide a review or leave as many reviews as they want, but they cant have multiple reviews for the same product. How do I model this?

5
  • @bbaird sorry so basically my data that im working with customers have the option to not provide a review or leave as many reviews as they want. But, they cant have like multiple reviews for the same product
    – James
    Sep 29 at 16:28
  • It's just saying that every review must have a customer and a product, not that every Customer or Product must have a review. (The image is a bit blurred for me, and the multiplicity on the Review end of the Product-Review relation looks likes it might not include 0; I would count that a design error.) Sep 29 at 16:44
  • @James - Yep. Otherwise you could have a non-customer writing reviews about non-existing products. Trolls would love that! Sep 29 at 16:59
  • Add a unique key on review (productID, customerID). Add a pre-insert trigger on the review table, that prevents inserting reviews if the customer hasn't bought the product. Sep 29 at 17:02
  • Not all constraints can be represented in an ERD. I believe an ERD can show a unique constraint. The unique constraint would prevent duplicate reviews, ie. the same customer reviewing the same product multiple times. Also, that unique key could be the primary key, it would allow you to drop "reviewID". Sep 29 at 17:06
2

Before I begin, let's address a few issues with your current data model:

  1. This is not a relational data model. You have no keys. You have defined pointers between tables and nothing more.
  2. Because this is not a relational model, implementing certain constraints regarding cardinality are more difficult to model, much less enforce.

Which leads us to:

IDs are not by themselves keys

Everyone loves to slap auto-incrementing integers on tables and call them keys. They are row pointers, nothing more. Keys guarantee uniqueness of data, and Ids are not data, they are generated by the system.

For example, in your current data model it would be permissible to insert the exact same information into the Product table until I run out of integers. There is no key.

IDs can be keys, but only in addition to another key

The primary key on Product in your example is most likely something along the lines of (ProductCategory, ProductName, Color). This is rather wide (in bytes), so it's reasonable to demote that key to an alternate key and make ProductId1 the primary key of the table.

In this case, we say that ProductId is a surrogate for the original primary key.

Limiting customers to 1 review per product

Within the context of your problem, the primary key of Review is (ProductId, CustomerId). If we try to insert two reviews of the same Product authored by the same Customer, this will violate the primary key and the insert will fail.

As a benefit, we can abandon the useless Id column, which means we do not need an additional index to access a Review for a given Product2

Other considerations

  1. ProductCategory should be it's own table and Product should have a foreign key constraint to that table to enforce consistency.

  2. Right now an Order can only fulfill a single Product. Usually this is implemented as Order followed by OrderLine (primary key (OrderId, LineNbr)) which would allow a Product to be sold at different prices (useful for sales/discounts).

  3. I know it's just an example, but for the love of all that is sacred and holy, do not store the credit card information in the Customer table. A Customer could have multiple payment methods, and in any case it's much safer to have that data encrypted and in a table with very limited access.


1A better key would be something like ProductCd which would reflect the manufacturer's part code, a barcode, or some internal signifier (internal to the business, not the database).

2You would still need an index on CustomerId in order to more quickly access the reviews of a particular Customer.

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.