Advice on MySQL and many-to-many in populating the Associative Entity

Question

I'm designing a database that contains a list of recipes and their ingredients and now i'm in the processing of linking the individual ingredients to their respective recipes. However, i'm having trouble deciding on the best course of action on how to populate the associative entity table.

I have my 3 tables,

CREATE TABLE Ingredient
(
ingredientID int PRIMARY KEY AUTO_INCREMENT,
ING VARCHAR(100)
);

CREATE TABLE Recipe
(
recipeID int PRIMARY KEY AUTO_INCREMENT,
recipeTitle VARCHAR(200),
prepText VARCHAR(10000),
cuisineType VARCHAR(100)
);

CREATE TABLE recipeIng
(
recipeID int,
ingredientID int,
PRIMARY KEY (recipeID, ingredientID)
);

The Ingredient table is populated from an XML file containing 200+ individual ingredients, which each auto assigned a different ID starting at 1.

The Recipe table is populated by another XML file, containing the recipe title, preparation method and cuisine type.

The recipeIng table is what i'm having trouble with, I assume it will have to be populated by hand. i.e manually matching all the ingredients to their recipes. Like this:

INSERT INTO recipeIng
VALUES(1, 1);
INSERT INTO recipeIng
VALUES(1, 2);
INSERT INTO recipeIng
VALUES(1,3);
INSERT INTO recipeIng
VALUES(1, 4);

With '1' being the id of the first recipe and '1', '2' etc being the individual ingredient ID.

However i'm unsure if this is the best approach for the populating the table and any advice would be helpful.

Note: When combined with the in-use method of populating this query works fine.

SELECT r.recipeTitle
FROM Recipe r
INNER JOIN recipeIng e ON e.recipeID=r.recipeID
INNER JOIN Ingredient i ON i.ingredientID = e.ingredientID
WHERE 'brown sugar' IN (i.ING);

Answer 1

By viewing your last questions, I think this approach is at least better than having a comma-separated list of ids in one of the tables. Having a list of comma-separated values would f.e. limit you in the number of connections, depending on the configuration you have for the field storing the list of values.

The way, you showed here, is the way I design mn-relations in relational databases, too. This is also what you'd get using tools like MySQL Designer, so I think it's the best way to store a many-to-many relation.

I haven't really found much documentation around this excepted by this article, which is linked at the Wikipedia page for many-to-many relations: http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php

Answer 2

I am going through this exact situation with my website, but I'm dealing with testimonials instead of recipes. The only thing I have come up with so far that may help you is to do tons of inserts at once. For example, if most of your recipes call for flour, then you could a select insert statement that would insert 'flour' into recipeIng for multiple recipeIDs.

Good luck!