0

I have two tables, Reviews and Critic

enter image description here

I'm pretty new to RDBMS and I was wondering if the connection between Review.rID and Critic.Review is redundant?

My reasoning is that a single critic can have many reviews but each review is unique so I need a way to enforce the uniqueness of the Reviews. I did this via Review.rID which is unique. I also need to express that multiple critics can leave multiple reviews.

However, I am already connecting the critic to the review by including a criticID within Review, so is it necessary for me to also connect a reviewID to a critic within Critic?

0

The review column in critic means a critic can only do one review, which would make that career very unfulfilling. A primary key uniquely identifies a row, it might not necessarily enforce the uniqueness of content. In your case, the rID does not prevent the same review from being inserted multiple times.

A reviewer may have multiple reviews (including of the same movie). A review must come from one reviewer (unless you want to allow a review written by multiple critics). Based on that model, you need the FK column cID in review and nothing in critic. Additionally, the cID column should have a not null constraint because a review must belong to a critic (unless you want reviews that don't belong to any critic).

The question now is how to prevent the same review from being INSERTed multiple times. The only reliable method I can think of is to hash the review content and store the result in a column with a unique constraint in the review table.

4
  • If I have the FK column cID in review and nothing in Critic, then what row in Critic does the FK in Review reference? Oct 18 '20 at 23:50
  • Also, the NOT NULL should go in the Review table correct? Since if I were to put it in the critic table, it would be unnecessary since Primary Keys cannot be redundant Oct 19 '20 at 0:59
  • CRITIC does not have a FK column to REVIEW. A row in CRITIC can have multiple rows in REVIEW.
    – Jim D
    Oct 19 '20 at 11:03
  • The NOT NULL constraint does not imply uniqueness, it means that column must have a non-null value. You should have a NOT NULL constraint on the cID column in REVIEW and mID (you can't have a review on a NULL movie).
    – Jim D
    Oct 19 '20 at 11:06
0

A foreign key can only refer to a primary or unique key of another table. The 1-M relation you drew between Critic.Review and Review.rID is impossible.

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.