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);
