How to structure my database model for a simple web application and organize my SQL data model-CodePudding

I am working on a web app. I have a question about the SQL parts. I am currently creating a food rating interface. The user should be able to login and then rate the food.

Currently I use a custom database (users) for the login page. I also use a separate database (review) for the reviews. my current database structure makes no sense.

In the attached image you can see the tables, they are currently separate databases with only this one table each. I use MySQL extension in VSCode.

How can I combine the tables into one database? I am looking for some ideas for this problem.

Preferably the username should only be needed for login and not have to be entered separately in the rating (as unfortunately still current).

In the future, the user should be directed after login, to a page where he can select his dish, then he should rate it. Each dish should save the ratings individually, to distinguish this of course.

CodePudding user response：

I suggest that you do use the user table as a reference for reviews, and also a table dish with the details of each dish.
Here are suggested table definitions, to fine-tune to your needs:

Credits to Mishi for the improvement to foreign key syntax to make it more portable.

I've put the creation of the table review last because some SQL engines check that the table exists when we declare a foreign key.

create table users
(
uid int primary key,
username varchar(25),
password varchar(25),
created_at date
);

create table dish
(
did int primary key,
nom varchar(25),
description varchar(250),
last_rating int
);

create table review
(
rid int primary key,
user_id int ,
dish_id int ,
user_rating int ,
user_reveiw varchar(100),
review_date datetime
foreign key (user_id) references users(uid),
foreign key (dish_id) references dish(did)
);

db<>fiddle here