How to structure a database with multiple join tables

Question

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?

Answer 1

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

Answer 2

At first glance it looks very good. However, I'm not sure why the category and ingredient category tables need parent ids.

Also, you'll need a many to many relationship between users and recipes. You currently have what looks like one to one.