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 database scheme

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?

database design 2

The sample object only includes data directly associated with a recipe. The ingredient category may be used to search or filter by ingredients but is not directly related to a recipe. — Akkumulator, Jun 11 '20 at 06:57

munch1324 · Accepted Answer · 2013-04-13T12:50:58.843

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

Thank you for your answer. Can you suggest a better way of organizing subcategorys? Would it be better to limit the level of subcategorys and create a table for each layer? I'm not quite getting what you want to say with "Can a step ingredient exist without a step". A `step ingredient` shouldn exist without a `step`. And a `step` should belong to a recipe. — Akkumulator, Apr 12 '13 at 15:25
In that case, a step ingredient shouldn't need a connection directly to the recipe, and a step should require a FK to a recipe. Subcategories can be tricky in sql, there are many different ways to organize tree-like structures http://stackoverflow.com/questions/2175882/how-to-represent-a-data-tree-in-sql. If you know the depth of the categorization, it may be easier to create leveled tables. — munch1324, Apr 12 '13 at 15:36
Do you think my second diagramm, I added later, would be better? — Akkumulator, Apr 13 '13 at 08:11
Yes, that looks better. The questions/statements I asked in my answer were designed to help you realize the shape of your own data. In future database design it can help to ask these types of questions to make sure the schema you designed reflects the underlying data. I'm also going to edit my answer — munch1324, Apr 13 '13 at 12:35

score 2 · Answer 2 · answered Apr 12 '13 at 12:56

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.

answered Apr 12 '13 at 12:56

Dan Bracuk

20,699
4
26
43

the parent ids are there for subcategorys. If a category references another category its a subcategory. if `parent_id` equals `Null` its a main category. I havent worked out the user handling yet. But thanks for pointing that out :) – Akkumulator Apr 12 '13 at 13:19
1

Instead of nullifying the parent id for main categories, I'd give it the same value as the category id. Null values in foreign key fields might be allowed in some databases, but they rub me the wrong way. – Dan Bracuk Apr 12 '13 at 15:51

How to structure a database with multiple join tables

2 Answers2