How to structure a database with multiple join tables
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?
The main thing with relational database design is that there are 3 types of FK relationships:
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
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.
链接地址: http://www.djcxy.com/p/86276.html上一篇: 错误代码1215 MySQL
下一篇: 如何用多个连接表构造数据库