Ok, I'm creating a bunch of interrelated tables. I'll try to simplify them greatly to just specify my question. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/smile.gif" style="vertical-align:middle" emoid="
" border="0" alt="smile.gif" /><br /><br />I'm going to basically have a table of items and a table of recipes (it's more convoluted than that, but I think I can get the point across with just this). Each recipe will require at least one item (in varying quantities), but perhaps multiple items to create. So I'm looking for the best way to make my tables so that I can easily display them, but also so that I can link the ingredients back to the ingredients table.<br /><br />Example...<br />Recipe table<br /> [*]recipeID (primary)<br /> [*]itemID (what is made)(forgein)<br /> [*]Ingredients (itemID of ingredient... not sure how to do this one yet to include quantity)<br /><br />Item Table<br /> [*]itemID (primary)<br /> [*]itemName<br /> [*]Notes<br /><br />Once finished, I want to have something like this....<br />RecipeName, Ingredient1 (quantity), Ingredient1 (quantity), Ingredient3 (quantity)<br /><br />Currently, with my single Ingredient field, I'm looking at parsing the results in php after I get the recipe to separate them. I don't really want to do that, but I'm not sure of a better way to do it.<br /><br />I could use....<br />Recipe table<br /> [*]recipeID (primary)<br /> [*]itemID (what is made)(forgein)<br /> [*]Ingredient (itemID of ingredient)<br /> [*]Quantiy (quanity of ingredient to use<br /><br />...and then have multiple lines for each recipe (one for each different ingredient). <br /><br />Thoughts?<!--content-->
Yep, you're thinking in the right way.<br /><br />You should do a separate table for ingredients. And possibly a table for type of measurement (but that's a little complex)<br /><br />Then, in your recipe table, you have a column for quantity.<br /><br />So it might look like:<br /><br />Item_ID: 1<br />Recipe_ID: 21<br />Ingredient: 4<br />Quantity: 2<br />Unit: 'Tablespoons' (or you could put an integer for unit_ID)<br /><br />And then the next line might look the same except for ingredient and quantity<br /><br />Item_ID: 1<br />Recipe_ID: 21<br />Ingredient: 10<br />Quantity: 3<br />Unit: 'Ounces' (or you could put an integer for unit_ID)<br /><br />So if you set your query up, and Ingredient 4 was 'Butter' and Ingredient 10 was 'Sugar' you'd get<br /><br />Mom's Apple Pie<br />2 Tablespoons of Butter<br />3 Ounces of Sugar<br />etc...<br /><br />Good luck.<!--content-->
Ok, I thought that was probably the best route to take, but thought I'd ask. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/smile.gif" style="vertical-align:middle" emoid="" border="0" alt="smile.gif" /><br /><br />Thanks a lot.<!--content-->
Ok, new question. Figure I'll keep it in this thread since it's similiar.<br /><br />Would you recommend doing the same for most any table that will have multiple (but of an unknown number) of items for a certain field (like the ingredients one)? Can you think of an exception to that?<br /><br />I ask because I have various tables that will have a field with multiple items in them. Unlike the recipe one though, the only real reason I would need them separated would be to control how they are output onto the page (it's just easier to control different fields than to parse the incoming string). For example, one of the game tables I have will list the quests that can be done. In the reward field there may be multiple rewards (you may get money as well as a new suit of armor or something). <br /><br />But the Quests table has about a dozen other fields in it besides the reward one, so I hate to repeat them all. Would it make good logic sense to pull out the reward list into another table and just link it by questID?<br /><br />- Vendlus<!--content-->
As you said<br /><!--quoteo--><div class='quotetop'>QUOTE</div><div class='quotemain'><!--quotec-->(it's just easier to control different fields than to parse the incoming string<!--QuoteEnd--></div><!--QuoteEEnd--><br /><br />And that's my feeling too. It takes more upfront investment in setting up your database tables and setting up more complex queries.<br /><br />But the end result is much better.<br /><br />Take apart open source scripts like Invision Board, phpBB, OSCommerce, and so on, and you'll see multiple tables. With complex queries and relationships tying them together.<br /><br />Plus, I've heard that a computer can search for an integer more quickly than a string... but don't quote me on that.<br /><br />The real reason to do it is to make it easier in the long term to manage your site.<br /><br />Now, I will switch gears and tell you that there are situations I've run into in the past where I will serialize() the data into a string and save the variables in a text field instead of multiple rows of the same table.<br /><br />I think the reason I did it is because I hadn't yet set up a good admin page and so I was going to have to be editing the table directly in phpMyAdmin. And for me, if the table has severl hundred rows, it's just easier to edit the data if I only have to edit one row...<br /><br />It's really just a matter of preference.<!--content-->
" border="0" alt="smile.gif" /><br /><br />I'm going to basically have a table of items and a table of recipes (it's more convoluted than that, but I think I can get the point across with just this). Each recipe will require at least one item (in varying quantities), but perhaps multiple items to create. So I'm looking for the best way to make my tables so that I can easily display them, but also so that I can link the ingredients back to the ingredients table.<br /><br />Example...<br />Recipe table<br /> [*]recipeID (primary)<br /> [*]itemID (what is made)(forgein)<br /> [*]Ingredients (itemID of ingredient... not sure how to do this one yet to include quantity)<br /><br />Item Table<br /> [*]itemID (primary)<br /> [*]itemName<br /> [*]Notes<br /><br />Once finished, I want to have something like this....<br />RecipeName, Ingredient1 (quantity), Ingredient1 (quantity), Ingredient3 (quantity)<br /><br />Currently, with my single Ingredient field, I'm looking at parsing the results in php after I get the recipe to separate them. I don't really want to do that, but I'm not sure of a better way to do it.<br /><br />I could use....<br />Recipe table<br /> [*]recipeID (primary)<br /> [*]itemID (what is made)(forgein)<br /> [*]Ingredient (itemID of ingredient)<br /> [*]Quantiy (quanity of ingredient to use<br /><br />...and then have multiple lines for each recipe (one for each different ingredient). <br /><br />Thoughts?<!--content-->Yep, you're thinking in the right way.<br /><br />You should do a separate table for ingredients. And possibly a table for type of measurement (but that's a little complex)<br /><br />Then, in your recipe table, you have a column for quantity.<br /><br />So it might look like:<br /><br />Item_ID: 1<br />Recipe_ID: 21<br />Ingredient: 4<br />Quantity: 2<br />Unit: 'Tablespoons' (or you could put an integer for unit_ID)<br /><br />And then the next line might look the same except for ingredient and quantity<br /><br />Item_ID: 1<br />Recipe_ID: 21<br />Ingredient: 10<br />Quantity: 3<br />Unit: 'Ounces' (or you could put an integer for unit_ID)<br /><br />So if you set your query up, and Ingredient 4 was 'Butter' and Ingredient 10 was 'Sugar' you'd get<br /><br />Mom's Apple Pie<br />2 Tablespoons of Butter<br />3 Ounces of Sugar<br />etc...<br /><br />Good luck.<!--content-->
Ok, I thought that was probably the best route to take, but thought I'd ask. <img src="http://www.totalchoicehosting.com/forums/style_emoticons/default/smile.gif" style="vertical-align:middle" emoid="
" border="0" alt="smile.gif" /><br /><br />Thanks a lot.<!--content-->Ok, new question. Figure I'll keep it in this thread since it's similiar.<br /><br />Would you recommend doing the same for most any table that will have multiple (but of an unknown number) of items for a certain field (like the ingredients one)? Can you think of an exception to that?<br /><br />I ask because I have various tables that will have a field with multiple items in them. Unlike the recipe one though, the only real reason I would need them separated would be to control how they are output onto the page (it's just easier to control different fields than to parse the incoming string). For example, one of the game tables I have will list the quests that can be done. In the reward field there may be multiple rewards (you may get money as well as a new suit of armor or something). <br /><br />But the Quests table has about a dozen other fields in it besides the reward one, so I hate to repeat them all. Would it make good logic sense to pull out the reward list into another table and just link it by questID?<br /><br />- Vendlus<!--content-->
As you said<br /><!--quoteo--><div class='quotetop'>QUOTE</div><div class='quotemain'><!--quotec-->(it's just easier to control different fields than to parse the incoming string<!--QuoteEnd--></div><!--QuoteEEnd--><br /><br />And that's my feeling too. It takes more upfront investment in setting up your database tables and setting up more complex queries.<br /><br />But the end result is much better.<br /><br />Take apart open source scripts like Invision Board, phpBB, OSCommerce, and so on, and you'll see multiple tables. With complex queries and relationships tying them together.<br /><br />Plus, I've heard that a computer can search for an integer more quickly than a string... but don't quote me on that.<br /><br />The real reason to do it is to make it easier in the long term to manage your site.<br /><br />Now, I will switch gears and tell you that there are situations I've run into in the past where I will serialize() the data into a string and save the variables in a text field instead of multiple rows of the same table.<br /><br />I think the reason I did it is because I hadn't yet set up a good admin page and so I was going to have to be editing the table directly in phpMyAdmin. And for me, if the table has severl hundred rows, it's just easier to edit the data if I only have to edit one row...<br /><br />It's really just a matter of preference.<!--content-->