I’m working on a MySQL database project for a car review application. The aim of the application is to enable users to input a review on a car model as well as comments which other users can then view.
The schema consist of about 4 databases. One for customers, car models, car reviews containing “car_id, user_id, and review(out of 5)” as columns, called “car_user_reviews”. Another called “car_overall_ratings” that has “car_id, overall_rating” as columns. The overall rating is determined by calculating the average from every review of a specific car model.
However, a problem arises, that I’d like to fix: Users can submit multiple ratings for the same car model.
Approach:
Within the “car_user_reviews”, I’d like to know if there is a SQL query to allow the user_id column values to repeat, but only for different car_id values.
I am also considering approaches that involve redesigning the database to solve the problem.
Running on server 8.0.26
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Simply put a unique constraint on the combination of the columns.
ALTER TABLE car_user_reviews
ADD UNIQUE (user_id, car_id);(And you should say “table” when you mean a table, not “database”. Database is the whole thing not just a table.)
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0