How to save dynamic text into database effeciently?

Question

I have this kind of text and want to save it to database:

I. Master Text I                                            <<<Parent
   I.A. Sub Master Text I                                   <<<Child
      I.A.1.  Sub Sub Master Text I                         <<<Child Child                    
         I.A.1.a Sub Sub Sub Master Text I - Row 1          <<<Child Child Child 1
         I.A.1.b Sub Sub Sub Master Text I - Row 2          <<<Child Child Child 2
         I.A.1.c Sub Sub Sub Master Text I - Row 3          <<<Child Child Child 3

II. Master Text II
   II.A. Sub Master Text II
      II.A.1.  Sub Sub Master Text II
         II.A.1.a Sub Sub Sub Master Text II - Row 1
      II.A.2.  Sub Sub Master Text II
         II.A.2.a Sub Sub Sub Master Text II - Row 1
         II.A.2.b Sub Sub Sub Master Text II - Row 2

III. Master Text III
   III.A. Sub Master Text III
      III.A.1.  Sub Sub Master Text III
         III.A.1.a Sub Sub Sub Master Text III - Row 1
         III.A.1.b Sub Sub Sub Master Text III - Row 2
         III.A.1.c Sub Sub Sub Master Text III - Row 3
   III.B. Sub Master Text III

How to effeciently save these text?. There are always a Parent but the number of Child is very dynamic. I can't predict how many child would be exist.

I currently using a conventional Master Table and Detail Table but I have to define the number of the Detail Table first. So, it's not reliable for this kind of text.

My main question, How to effeciently save these text (with n child) into database and easily display it like in the example?

Yuriy Marad · Answer 1 · 2019-04-30T10:07:50.367

It's "hierarchical data" topic. I would recommend you to look at this great presentation and choose the best way for you.

However MySql 8 support recursive with clause, so you can use something like this:

CREATE TABLE `texts` (
  `id` INT NOT NULL,
  `text` TEXT NULL,
  `parent_id` INT NULL,
  PRIMARY KEY (`id`));

And example for select sub items which follow 1st tree:

with recursive cte (id, text, parent_id) as (
  select     id,
             text,
             parent_id
  from       texts
  where      parent_id = 1
  union all
  select     p.id,
             p.text,
             p.parent_id
  from       texts p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

Here you can find great answer about with clause and analogs for older versions.

How to save dynamic text into database effeciently?

1 Answers1