how to insert tab delimited file into mysql with relation

Question

What i want is to insert data in mysql table but i unable to find the way to make relationship from one row

suppose i have a file file.tab it contains data in like

parent_1    parent_details_1    child_1.1   child_details_1.1   child_1.2   child_details_1.2
parent_2    parent_details_2    child_2.1   child_details_2.1
parent_3    parent_details_3    child_3.1   child_details_3.1   child_3.2   child_details_3.2   child_3.3   child_details_3.3

what i want to achive is to insert data in two table like

            parent_table
+---+-----------+-------------------+
|id |   name    |      details      |
+---+-----------+-------------------+
| 1 |  parent_1 |  parent_details_1 |
| 2 |  parent_2 |  parent_details_2 |
| 3 |  parent_3 |  parent_details_3 |
+---+-----------+-------------------+


              child_table
+---+-----+-----------+-------------------+
|id | pid |   name    |      details      |
+---+-----+-----------+-------------------+
| 1 |  1  | child_1.1 | child_details_1.1 |
| 2 |  1  | child_1.2 | child_details_1.2 |
| 3 |  2  | child_2.1 | child_details_2.1 |
| 4 |  3  | child_3.1 | child_details_3.1 |
| 5 |  3  | child_3.2 | child_details_3.2 |
| 6 |  3  | child_3.3 | child_details_3.3 |
+---+-----+-----------+-------------------+

first two columns is for parent and after that two-two columns belongs to child but i don't know how many child a parent has.

i have tried to load file that way.

LOAD DATA INFILE '/tmp/file.tab INTO TABLE ... 

but what do i do next i have no idea.

so kindly help me out in this question.

Answer 1

Create a table (Staging) with lots of columns. Have empty (NULL) columns for parent_id and ids for the children.

Hope that the 'short' lines will put nulls in the missing children columns during the LOAD DATA.

INSERT .. SELECT .. to get the parent and parent_detail into the Parents table. Pull back the ids from Parents into Staging.parent_id. The details on the two SQLs for these are in http://mysql.rjweb.org/doc.php/staging_table#normalization

Now do something similar for each possible "child" set of columns: child1 and child1_detail (possibly NULL pair) and the currently NULL child1_id. Ditto for child2*, etc. Note that when populating the Children table, you already have parent_id available.

This is an all-SQL way of doing the task. It is only slightly less messy than writing Perl/PHP/Java/VB/whatever code to do the task.