如何构造具有多个联接表的数据库

Question

这是我第一次尝试将更复杂的对象存储到数据库中。我需要一些关于数据库设计的帮助。

要存储并从数据库中重新生成的recipe对象

{
    "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
                }
            ]
        }
    }
}

最复杂的部分是steps对象。每个食谱可以有不同数量的步骤和不同的配料分配给每一个步骤。

-- -----------------------------------------------------

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

我做了另一个设计，其中食谱与步骤以及步骤与成分相结合。我认为第一种布局更容易查询，因为我只需查看step_firedents_idrecipe_id就可以通过step_firedents进行搜索，但我不确定。有什么想法吗？

Answer 1

关系数据库设计的主要内容是有3种类型的FK关系：

• 1到1
• 1到多
• 多对多

话虽如此，但您的模式一目了然地看起来很规范、很合乎逻辑。我要提醒的唯一一点是，对于带有self引用的类别，SQL中的递归可能会很棘手。

一个步骤可以在没有制作方法的情况下存在

用户与食谱是一对一的（正如Dan所提到的）

编辑：对于从菜谱到成分的双连接而不是单连接的问题，这里有一个与原始设计有关的标准化问题：是什么使step_ingredent和steprecipe_id保持相同的？现在还不能保证一贯性。如果您考虑数据设计，您实际上是说您认为您会连接这两个表很多，那么为什么不用一个不必要的FK将它们连接起来（不要这样做，否则事情会很快变得乱七八糟：））

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