mysql - 如何构建具有多个连接表的数据库

Question

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

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

{
    "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 对象。每个食谱可以有不同数量的步骤，每个 setp 分配不同的成分。

这是我制作的数据库设计

recipe_id, step_id 是外键。我想要不同表格中的所有内容，因为食谱应该可以按成分、类别排序...

SQL 代码 用于生成最重要的表

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

因为我以前从未做过连接表，所以我不知道这是否是解决我的问题的正确方法。设计是否合理，如何优化？

我做了另一个设计，其中 recipes 与 step 相结合，step 与 ingredients 相结合。我认为第一个布局更容易查询，因为我可以通过 ingredients_id recipe_id 仅查看 step_ingredients 进行搜索，但我不是当然。有什么想法吗？

Answer 1

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

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

话虽如此，您的架构乍一看看起来规范化且合乎逻辑。我要提出的唯一警告是，对于具有自引用的类别，递归在 SQL 中可能很棘手。

一些注意事项:

步骤成分需要一个已经有 recipe_id 的步骤 (可能为空)

可以没有步骤的步骤成分存在吗

一个步骤可以没有配方

食谱的用户是一对一的(如 Dan 提到)

编辑:对于从配方到成分的双连接而不是单连接的关注，这是我对原始设计的一个规范化关注:是什么让 step_ingredient 和 step recipe_id 相同？现在，无法保证一致性。如果您考虑数据设计，您实际上是在说您认为您会经常加入这两个表，所以为什么不将它们与不必要的 FK 连接起来(不要这样做，否则事情会很快变得困惑:))

您的第二个设计实际上也允许相同数量的连接，因为您已将 recipe_id 作为 PK 包含在步骤表中，然后在 step_ingredient 中成为 PK/FK，这将保证 recipe_id 的一致性。例如:

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

我最喜欢的数据库规范化入门链接:http://en.wikipedia.org/wiki/Database_normalization