What would be the best way to structure this database?

metomeya

Hi guys,

Hoping I can find some help.

I just installed Xampp and I'm trying to use PhpMyAdmin to create a database to do the following:

A diet plan that will pull from a database of meals (Breakfast, Lunch, Dinner, Snacks) and create a meal plan for 7 days like this

Monday Tuesday Wednesday Thursday, etc.
-Breakfast -Breakfast
-Lunch -Lunch
-Snack 1 -Snack 1
-Dinner -Dinner
-Snack 2 -Snack 2

(Snack 1 and 2 will pull from the same database)

It will also produce a grocery list for the week

So basically the Php/MySql should produce 3 outputs (to MS Word):
1.) A short list of the meals for 7 days
2.) A long list of the meals for 7 days
(includes ingredients, and cooking directions)
3.) A grocery list

So I need some help on the database structure does this seem about right??

Database: Diet

Table 1: Breakfast
Brkfast_Key Auto_Increment
Brkfast_Name
Ingredient
Ingredient_Amt
Ingredient_Amt_label
Brkfast_Description

Table 2: Lunch
Lunch_Key Auto_Increment
Lunch_Name
Ingredient
Ingredient_Amt
Ingredient_Amt_label
Lunch_Description

Table 3: Dinner
Dinner_Key Auto_Increment
Dinner_Name
Ingredient
Ingredient_Amt
Ingredient_Amt_label
Dinner_Description

Table 4: Snack
Snack_Key Auto_Increment
Snack_Name
Ingredient
Ingredient_Amt
Ingredient_Amt_label
Snack_Description

Table 5: Ingredients
Ingredient

Table 6: WeekMeals
Day_of_Week
Brkfast_Name
Brkfast_Description
Lunch_Name
Lunch_Description
Dinner_Name
Dinner_Description
Snack_Name
Snack_Description
Ingredient
Ingredient_Amt
Ingredient_Amt_label

Table 7: GroceryList
Ingredient
Ingredient_Amt
Ingredient_Amt_label

I'm brand new to MySql, PhpMyAdmin, and Php. I've only taken a short computer system class in college that cover some information on Microsoft Access so any help would be greatly appreciated.

Thanks!

johnpeace

I actually manage a recipe application.

We keep ingredients in their own table and ingredient measurement units (tsp, tbsp, sprig, pinch, splash, etc) in their own tables.

So, a recipe is made up of ingredients and their measurements in measurement units.

A meal is made up of recipes.

A week diet is made up of meals. (thought we don't ever really get into meals and diets, just recipes)

The goal here is that a recipe shouldn't have a field that contains multiple values for ingredients.

You should probably, for normalization's sake, have a recipe entry and then let that recipe key to a recipe_ingredients table for it's ingredients.

metomeya

So how many tables you think i can cut it down to for simplicity?

2 or 3?

1 for ingredients
1 for the meals

everything else is generated and posted to a word document?

metomeya

Okay I under what what you are saying (wish I could delete my old post)

johnpeace

Oddly enough, cutting down on the number of tables in a relational database doesn't really, necessarily make any contribution to it's simplicity.

NAMING tables, on the other hand...

The whole idea of the relational database is tables with relationships to one another. So, when your modeling complex stuff like meals (or recipes, or articles, or users, or any of the thousands of things we are asked to model in the apps we build) it usually makes more sense to break things apart into smaller pieces, each occupying their own table.

So recipes is related to ingredient_types is related to ingredients which is related to ingredient_measurements.

The better you build your DB, the more tables it will have.

Weird, huh?

There is no real 'SQL genius' you can attain that will mean you can build relational databases with the absolute fewest number of tables possible.

Quite the opposite.

The more normalized your data, the more tables you're going to have.

Just write good documentation describing the database, each of it's tables, where they relate to other tables, etc.

Make sure your PHP classes include this documentation or a link to it so anyone extending it down the road can have a glimpse into the mind of the guy who built the database in the first place.

halojoy

Oddly enough, cutting down on the number of tables in a relational database
doesn't really, necessarily make any contribution to it's simplicity.

Agree 100%
The most frequent mistake is to create too few tables, with lots of columns/data in it.
This often makes for VERY complicated query. And data not easy to handle.

Experienced and good database people often makes quite many tables
some with only 2-3 columns.

Of course we should not make too many tables.
Only those tables we need for a good structure of data
based on the results we want to pull out of the database.

metomeya

Okay I updated the tables, tell me what you think

MealTypes
--------------------
typeID | typeName
--------------------
1 | breakfast
2 | lunch
3 | dinner
4 | snack

Ingredients
--------------------
IngreID | IngreName
--------------------
1 | Eggs
2 | Apple
# | ...

Unit
--------------------
UnitID | UnitName
--------------------
1 | tsp
2 | tbsp
# | ...

Meals
--------------------
MealID | Meal Name | typeID | IngreID | IngreAmt| UnitID | description    |
--------------------
1 | "Scrambled Eggs" | 1    |    1    |     3   |    0   | "4 egg whites" |

The things I'm wondering about (a very noob question), since each meal will obviously will need several ingredients, will the arrangement be okay this way? Is there a special command I will have to do?

metomeya

Am I not allowed to edit my last post? Cause "4 egg whites" doesn't work with 3 eggs.