Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Shane Meikle
13,188 PointsHow would I structure this MySQL table correctly?
Current table looks like this:
Ingredient_1 | Ingredient_1_amount |Ingredient_2 | Ingredient_2_amount (and continues on until ingredient_10)
A row would look like:
Onions | 300 | Pineapple | 10 | Beef | 30
Is this the correct way to structure a table of this type (ie. ingredient/amount combinations) or is there another way?
Shane Meikle
13,188 PointsThere isn't an application, it is more of an exercise in learning how to properly build tables.
Basically, the format is there is an item, and that item uses ingredients to be made. I want to associate the ingredients and their amounts to the item.
PHP example:
$item['somethinghere'] = array (
"ingredient" => 100,
"ingredient2" => 250,
"ingredient3" => 170,
);
So basically converting that array into a mysql table
1 Answer
Gareth Borcherds
9,372 PointsYep, you would want to setup something more generic like Ingredient_type | ingredient_amount | ingredient_measurement_type
That way you can store the ingredient, an amount, and what that amount is measured in, but you might not need that. Some sample data might be Onion | 300 | grams Pineapple | 10 | ounces
But then you might need some sort of ID column as well to reference to a recipe or whatever else you're trying to build. Does that help?
Shane Meikle
13,188 PointsThanks for the response, and yeah, it helps but I don't think it will work in this case.
I think I might have to just go with it as is and filter out any null values if all ingredient fields are not used.
Gareth Borcherds
9,372 PointsI'm pretty sure this table design would work for any application. Can you share a few more details? I can probably help you see how to use this structure better as this is the structure databases should be built in.
Shane Meikle
13,188 PointsI've tried displaying tables before and I cannot seem to do so on these forums, so I am using a simplified concept to try and explain.
What I am looking to do is have a table that has a list of items and the ingredients/amounts to make each item. Some items have many ingredients, some only a couple. The ingredient amounts also vary.
This isn't a major project or anything, just something I am using to learn more about using MySQL.
Gareth Borcherds
9,372 PointsYou simply need two tables to accomplish this. One is for the item and another one is for the ingredients. A lot of times I find people trying to relate a database to a spreadsheet, but they really are completely different. Here's how I would structure the tables:
First the item table
item_id | item_name
Then the ingredients table
ingredient_id | item_id | ingredient_type | ingredient_value
For each item you can have multiple ingredients, as many as you want or as little as you want. This structure allows you to have more than 10 ingredients in a very nice structure. In database terms, this would be a one to many relationship.
Some example data would be like this:
Item table
1 | Chicken Soup
Ingredients Table
- 1| 1| chicken | 1 pound
- 2| 1| onion | 1 onion
- 3| 1| water | 3 cups
- etc
So the second column with the 1 relates back to the Chicken Soup item and you can query the ingredients table based on the item_id from your items table and find it in column 2 of your ingredients table.
Is this helping? Making any sense?
Shane Meikle
13,188 PointsPerfect example, I think that is what the person above might have been alluding to as well. Thank you!
Jeff Busch
19,287 PointsJeff Busch
19,287 PointsHi Shane,
If I understand what you're trying to accomplish here you really only need two columns. One for ingredient and another for amount. Then every ingredient would create it's own row. Is this for an inventory? It would help to know what the application is.
Jeff