Cookbook table structure

[thebadbad]

I'm creating an online cookbook, and need some advice on how to structure the database tables.

 

Here's my idea: I'll need a recipes table containing the titles of the recipes, cooking procedure and corresponding recipe IDs. Then an ingredients table containing all ingredients and corresponding ingredient IDs. And lastly, one or several tables linking ingredients (via the ingredient IDs) with the recipes (via the recipe IDs), including the amount of each ingredient that goes into each recipe, along with a unit (e.g. litre or tablespoons).

 

The structures of the first two tables are straight forward, but I need some advice on how to structure the last one(s) linking recipes with ingredients.

 

The only solution I can think of includes storing of delimited strings, which is inefficient and bad practice I'm sure.

 

Thanks in advance!

[thebadbad]

Came up with this reasonable solution for the table that links the recipes with the ingredients:

 

+-----------+---------------+--------+----------+

| recipe_id | ingredient_id | amount | unit    |

+-----------+---------------+--------+----------+

|        1 |            1 |      3 | litre    |

|        1 |            2 |      1 | teaspoon |

+-----------+---------------+--------+----------+

 

But I would still like some feedback or suggestions for improving the structure.

 

As part of the site, I'd like to include a feature where you can tell which ingredients you have, and get a list back of meals to make from those. The above structure wouldn't be very efficient when dealing with that, right?

[fenway]

As part of the site, I'd like to include a feature where you can tell which ingredients you have, and get a list back of meals to make from those. The above structure wouldn't be very efficient when dealing with that, right?

Why not... simply compare a list of recipes with total # of ingredients to the number of ingredients available.