What is the best way to store recipe ingredients in MySQL for a cooking recipe website?

When storing recipe ingredients in MySQL for a cooking recipe website, it is best to create a separate table for ingredients and establish a many-to-many relationship with the recipes table using a junction table. This allows for flexibility in managing ingredients and their quantities for each recipe. 

// Create ingredients table
CREATE TABLE ingredients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

// Create junction table for recipe ingredients
CREATE TABLE recipe_ingredients (
    recipe_id INT,
    ingredient_id INT,
    quantity VARCHAR(50),
    PRIMARY KEY (recipe_id, ingredient_id),
    FOREIGN KEY (recipe_id) REFERENCES recipes(id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
);

Keywords

PHP MySQL database design data modeling normalization

Related Questions