MySQL Database Design – How to Model Relationships Between Three Tables

database-designMySQLmysql-workbench

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?

Best Answer

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

Related Solutions

Mysql – How to model this relationship

If an invitations is just an invitation, then it seems simplest to store all of the users for an invite in their own row in a separate table.

Users
 - UserID, PK
 - other details about the users

Invitations
 - InvitationID, PK
 - OwnerID or OwningUserID or just UserID, FK to Users
 - other details about the invitations

InvitationUsers
 - InvitationID, FK to Invitations
 - UserID FK to Users
   [PK on (InvitationID, UserID)]

A lot of people are tempted to store all of the users of an invitation in a comma-separated list or as XML. Resist this temptation; it leads to nothing but trouble.

If there is only a single type of invitation (like "be my friend on Facebook and we can hug and all that") then a simpler model might be:

Users
 - UserID, PK
 - other details about the users

Invites
 - SenderID, FK to Users(UserID)
 - RecipientID, FK to Users(UserID)
   [PK on (SenderID, RecipientID)]

If an owner can send an invitation to various things (a dating service, a boat show and a movie) then we'll need more details about what those relevant things are.

Mysql – How to create Combination of records (Order does not matter, no repetition allowed) in theSQL tables

Like Mike says, this is not something that you should be doing with the database. This is more of an algorithm question.

Your problem looks very similar to the knapsack problem, which is NP-Complete. Fortunately, you are not looking for the set that adds up to exactly some amount of calories, but a range, so it's not NP-Complete. Luckily for you, a few hundred recipes will easily fit in memory, so you can easily write an algorithm for it.

Here's a simple algorithm. I'm sure there's a better way to do it:

orderedRecipes = recipes sorted by calorie asc

def combinations(recipes, lowerBound, upperBound, recipeCount):
    first = recipes[0]
    if first.calorie < upperBound:
        for c in combinations(recipes[1:], lowerBound - first.calorie, upperBound - first.calorie, recipeCount - 1):
            if sum(r.calorie for r in [first] + c) > lowerBound:
                yield [first] + c
        for c in combinations(recipes[1:], lowerBound, upperBound, recipeCount):
            if sum(r.calorie for r in c) > lowerBound:
                yield c

combinationsOf3Between440and460 = list(combinations(orderedRecipes, 440, 460, 3))

The first loop takes the first item in the rest of the list, and the second loop does not. You don't want it to always take the first item, you want both take and no-take.

Best Answer

Related Solutions

Mysql – How to model this relationship

Mysql – How to create Combination of records (Order does not matter, no repetition allowed) in theSQL tables

Related Question