How to model the relationship between these three tables

Question

This is my first database, it is for a nutritional info meal tracker program. I have created a model in mySql work bench but I'm not sure if I have the relationships right. See image below..

My thinking is that each user would have a new table for each day and each day would have multiple meals. I was thinking that for each day the meal number would go back to 1 as it is the primary key. Would this be a problem i.e. have multiple meals with the same number or would I be able to identify them from the day and the meal number and if so how would I do that? I'm not sure if this is even the best way to design this database so any help would be appreciated. Cheers.

From Mr.Brownstone's comment I've come up with this new design. Is it possible to have the user_name in MEALS automatically filled in from USER?

Answer 1

I personally would not design it so that I had to create new tables all of the time. As you already track the user_id in the meals table, I would just add another column for the date to it and remove the day table altogether. I would advise to not make the date_time attribute as the primary key though because it is highly probable that more that one person will want to eat on the same day...

You also have a redundant attribute user_user_id in the meals table because it is already included as user_id.

The user_name field does not belong in the meals table because it is an attribute of a user. You can however include the user_name in the query used to obtain meals items like so:

SELECT `date_time`
     , u.`user_id`
     , u.`user_name`
     , `calories`
     , `fat`
     , `carbohydrates`
     , `carbs_which_sugar`
     , `fibre`
     , `protein`
  FROM `meals` AS m
  JOIN `users` AS u ON u.`user_id` = m.`user_id`;