[SQL] Set constraint so that column value can repeat on certain conditions

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

Continue reading...