-1

I have 6 tables with ~2-15 attributes each, each one of these 6 tables has an ID which is constrained from another table. This 7th table has its own ID and the 6 other table ID's, as well as a date and comment attribute. This is the first 3NF database I have made, with this many attributes and I am confused on how when I add data to the first 6 tables, the id's will be inserted into the 7th.

enter image description here

Sorry for cutting out the table attribute names, its not top secret or anything i'd just rather not put the information online.

As for what exactly I need to do, is create a record relating each of those tables to a single table.

closed as unclear what you're asking by mustaccio, MDCCL, LowlyDBA, Md Haidar Ali Khan, Vérace Oct 14 '18 at 15:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

Generally you must insert the records into "slave" tables and then insert records into "main" table selecting slave IDs by the values just inserted:

INSERT INTO slave1 (field1_1, ...) 
    VALUES (value1_1, ...);

INSERT INTO slave2 (field2_1, ...) 
    VALUES (value2_1, ...);

...

INSERT INTO main (slave1_id, slave2_id, ...)
    VALUES ( (SELECT id FROM slave1 WHERE field1_1 = value1_1 AND ...),
             (SELECT id FROM slave2 WHERE field2_1 = value2_1 AND ...),
             ...
           );

In some cases (when you can guarantee that all queries will be executed in the same connection or your connector allows multi-queries, and all slave IDs are auto-increments) you can obtain LAST_INSERT_ID() for each slave table and use it while inserting into main table.

INSERT INTO slave1 (field1_1, ...) 
    VALUES (value1_1, ...);

SET @id1 := LAST_INSERT_ID();

INSERT INTO slave2 (field2_1, ...) 
    VALUES (value2_1, ...);

SET @id2 := LAST_INSERT_ID();

...

INSERT INTO main (slave1_id, slave2_id, ...)
    VALUES ( @id1, @id2, ... );

And the best solution (I think) is to use a stored procedure which accepts all parameters for all tables and performs all actions you need.

Of course, you must consider that any of inserts can fail by any reason, and take all actions to avoid inconsistent data. Transaction can help, for example.

  • Thanks for your clarification. I did consider using a transaction but I was unsure if this would be ideal or not. – Bender Bending Oct 12 '18 at 13:51
  • Don't over-normalize. In particular, do not normalize dates, floats, and anything else that is 'continuous'. Without this warning, doing a "range" on such a value can have terrible performance. – Rick James Oct 18 '18 at 2:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.