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
You'll likely want a
UserBookReviews bridge table to help manage the many-to-many relationship between
Users. You would store the key of each of those respective tables each in it's own column in the
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
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.
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.