Hello. There is a database: enter image description here

The ingredient table is associated with the recipe, product and cooking_method tables by the M: M link, but since physically such a relationship is not feasible, then intermediate tables with key fields from the corresponding tables and the M: 1 relationship are inserted between these tables. There was a difficulty - when adding an entry to the ingredient table, you probably need the intermediate tables to be filled in automatically. For example, I add an ingredient, select which recipe it belongs to, and enter its mass / quantity (amount field in the r_i table), depending on the unit of measurement (unit table), and an entry with id_recipe, id_ingredient and amount should appear in the intermediate r_i table. How to implement it?

  • In my humble opinion, you see the problem a little bit wrong. Ingredient does not depend on the recipe, the table of ingredients is replenished by itself. When compiling a recipe, the already existing ingredients and their quantities are entered in the table r_i, the same as in the classic example of orders. Products - ingredients, Orders - recipes consisting of OrderLines - r_i, which refer to the product_id and quantity of this product. - Sergey
  • @Sergey how should the i_p table be filled in automatically? - unit
  • Create a trigger on the table in which you want to track changes. In the trigger, set the conditions for which you want to update the intermediate tables. - AkaInq
  • No, it is not automatically filled. And it is not possible. How, for example, will the quantity that in r_i be filled, for example? Do you plan to use the power of the thought of the cook? How much to drop mayonnaise? I just thought, and without touching the keys of the computer, 100g dropped into r_i. - Sergey
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

MySQL has a notion of a trigger. It can be declared as follows.

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); CREATE TRIGGER trig_name BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; SET @sum = 0; INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); SELECT @sum AS 'Total amount inserted';

keyword NEW will take the current value in which this table will lie.

In your case, I think you can try as follows.

If you first write to the recipe table, you can remember its id in some variable, for example @recipe_id .

Then, when writing to an ingredient , the trigger will trigger and write both results to the pivote (linking - r_i ) table.

 CREATE TRIGGER trig_name BEFORE INSERT ON ingredient FOR EACH ROW (INSERT INTO r_i(id_recipe,id_ingredient) VALUES(@recipe_id,NEW.id));

The logic in my opinion is correct for your decision. The main thing is to write SQL. http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html Well, if you don’t want to, you’ll have to get acquainted with MySQL procedures, well, or write in parallel and in the third table.

I hope helped, good luck.

  • Just where does the amount appear in r_i? - Sergey