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.
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_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`;