How to structure a database with multiple join tables
database, database-design, join, mysql, sql
Solution
The main thing with relational database design is that there are 3 types of FK relationships:
- 1 to 1
- 1 to many
- many to many
That being said, your schema looks well normalized and logical at a glance. The only caution I would put in is that recursion can be tricky in SQL for the categories with self references.
A few notes:
A step ingredient requires a step which already has a recipe_id (possibly null)
Can a step ingredient exist without a step
A step can exist without a recipe
User to recipe is one to one (as Dan mentioned)
Edit: For the concern about the double join instead of a single join to go from recipe to ingredient, here is a normalization concern I hard with the original design: what keeps the step_ingredient and step recipe_id's the same? Right now, there would be no guarantee of consistency. If you consider the data design you are really saying you think you'll join these two tables a lot so why not connect them with a unnecessary FK(don't do this or things will get messy fast:) )
Your second design actually also allows the same number of joins because you have included the recipe_id as a PK in the step table which then becomes a PK/FK in the step_ingredient and it will guarantee recipe_id consistency. ex:
SELECT ingredient_id
FROM Recipe r
JOIN Step_ingredient si on si.step_recipe_id = r.recipe_id
JOIN Ingredient i on si.ingredient_id = i.ingredient_id
and my favorite link to getting started with database normalization: http://en.wikipedia.org/wiki/Database_normalization
Problem
This is the first time I'm trying to store a more complex object into a database. I need some help with the design of the database. The recipe Object I want to store and regenerate from the database ``` { "id": 2345, "name": "cake", "description": "yummy cake", "categorys": [ 17, 26 ], "persons": 4, "author": 26, "language": "de", "unit": "en", "variantOf": 34, "specialTools": [ 34, 44, 10 ], "img": "32598734.jpg", "estTime": 2777, "steps": { "1": { "title": "mix", "description": "mix all together", "img": "45854.jpg", "timer": null, "ingredients": [ { "name": "Butter", "color": "#227799", "amount": 150, "unit": "g" }, { "name": "egg", "color": "#aaff22", "amount": 3, "unit": "pc" }, { "name": "sugar", "color": "#22ffff", "amount": 50, "unit": "g" } ] }, "2": { "title": "bake", "description": "put it in the oven", "img": null, "timer": 2400, "ingredients": [ { "name": "butter", "color": "#227799", "amount": null, "unit": null }, { "name": "sugar", "color": "#22ffff", "amount": null, "unit": null }, { "name": "egg", "color": "#aaff22", "amount": null, "unit": null } ] } } } ``` The most complex part is the `steps` object. Each recipe can have a various number of steps with different ingredients assigned to each setp. Here is a database design I made `recipe_id, step_id` are foreign keys. I want everything in different tables, because the recipes should be sortable by ingredients, categorys... SQL code for generating most important tables ``` -- ----------------------------------------------------- -- Table `dev_Recipe`.`recipe` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `dev_Recipe`.`recipe` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(255) NULL , `description` TEXT NULL , `author_id` INT UNSIGNED NOT NULL , PRIMARY KEY (`id`) , INDEX `author_id_idx` (`author_id` ASC) , CONSTRAINT `author_id` FOREIGN KEY (`author_id` ) REFERENCES `dev_Recipe`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `dev_Recipe`.`step` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `dev_Recipe`.`step` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `recipe_id` INT UNSIGNED NOT NULL , `step_number` INT UNSIGNED NOT NULL , `description` TEXT NULL , `timer` INT UNSIGNED NULL , `image` VARCHAR(100) NULL , PRIMARY KEY (`id`) , INDEX `recipe_id_idx` (`recipe_id` ASC) , CONSTRAINT `step_recipe_id` FOREIGN KEY (`recipe_id` ) REFERENCES `dev_Recipe`.`recipe` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `dev_Recipe`.`ingredient` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `dev_Recipe`.`ingredient` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `color` INT NOT NULL , `img` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `dev_Recipe`.`step_ingredients` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `dev_Recipe`.`step_ingredients` ( `recipe_id` INT UNSIGNED NOT NULL , `ingredient_id` INT UNSIGNED NOT NULL , `step_id` INT UNSIGNED NOT NULL , `amount` INT NULL , `unit` VARCHAR(25) NULL , INDEX `recipe_id_idx` (`recipe_id` ASC) , INDEX `ingredient_id_idx` (`ingredient_id` ASC) , INDEX `step_id_idx` (`step_id` ASC) , PRIMARY KEY (`recipe_id`, `step_id`) , CONSTRAINT `step_ing_recipe_id` FOREIGN KEY (`recipe_id` ) REFERENCES `dev_Recipe`.`recipe` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `ingredient_step_ing_id` FOREIGN KEY (`ingredient_id` ) REFERENCES `dev_Recipe`.`ingredient` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `step_ing_id` FOREIGN KEY (`step_id` ) REFERENCES `dev_Recipe`.`step` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; ``` Since I've never done join tables before, I dont know if that is the right approach to my problem. Is it a reasonalbe design and how to optimize it? I made another design, where `recipes` is joined with `step` and `step` with `ingredients`. I think the first layout is more easy to query, because i can search by `ingredients_id` `recipe_id` by only looking at `step_ingredients`, but I'm not sure. Any thoughts?