I have an application where i can create and edit recipes consisting of ingredients and their amounts. This information is supposed to be saved in a relational database (probably MySQL or SQLite). I have a table storing recipes with an auto_increment primary key and another table where i save the ingredients and amounts, referencing the first table via a foreign key constraint.
Now i want to write the updating code inside my application. I save the recipe id's in my recipe objects, so i can reference that quite easily. But how do i properly update the ingredients and the amounts? Multiple rows of the second table might have become obsolete and have to be deleted, and some just need to be updated (when the amount was changed).
The simplest solution I came up with would be just deleting all the rows referring to my current recipe and re-inserting them. Is that an acceptable approach, or is there a pattern/best-practice for this kind of problem?
Note: I know i could just save the ingredients and amounts in varchar stringlist, but i would like to work with some kind of table structure.
Edit: The tables CREATE code:
CREATE TABLE `recipe` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL
PRIMARY KEY (`id`)
)
CREATE TABLE `recipe_ingredients` (
`recipe_id` INT(11) NOT NULL,
`ingredient` VARCHAR(30) NOT NULL,
`amount` FLOAT NOT NULL,
`measurement_unit` VARCHAR(10) NOT NULL,
INDEX `drink_id` (`drink_id`),
CONSTRAINT `recipe_ingredients_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipe` (`id`),
CONSTRAINT `recipe_ingredients_ibfk_2` FOREIGN KEY (`recipe_id`) REFERENCES `recipe` (`id`)
)
Aucun commentaire:
Enregistrer un commentaire