1

I am working on a database model with a many-to-many relationship. I want to create a database which will have books, users and reviews. Each book can have many reviews and each user can write many reviews. However, each user will be able to create only one review for each book. How can I do this? Do I have to create a trigger or is there another way of doing this? I am using MySql for the database

2 Answers 2

1

You'll likely want a UserBookReviews bridge table to help manage the many-to-many relationship between Books, Reviews, and Users. You would store the key of each of those respective tables each in it's own column in the UserBookReviews table.

So the columns in UserBookReviews could be (UserId, BookId, ReviewId) with the primary key defined on (UserId, BookId) which would guarantee uniqueness for a given Book that was reviewed by a particular User.

If you didn't want to define the primary key on those columns, then whichever database system you're using (note you should always tag this in your question) should also have the ability to create a UNIQUE CONSTRAINT or UNIQUE INDEX on those columns instead, which will still enforce your end goal.

1
CREATE TABLE Reviews (
    user_id ... ,
    book_id ... ,
    review TEXT ...,
    PRIMARY KEY(user_id, book_id),
    INDEX(book_id)
) ENGINE=InnoDB;

That's a many:many mapping table with good indexes. It also contains the "review" associated with the user--book pair.

In MySQL, the PRIMARY KEY is a UNIQUE key, thereby preventing a user from storing a second review for the same book.

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.