Understanding the MySQL Table Configuration of Parent-Child Hierarchy Data

What you want to do in the end
I want to display the parent-child hierarchy category in PHP

Q
After searching, I found [PHP][Recursive function version] that I tried to register and display categories of parents, children, and grandchildren. How do I configure mySQL table to get the following data in PHP?
·Create three tables: 'cat' 'subcat' 'subcat' ?
·Or can I get the data of the following configuration regardless of the table configuration?

46$files=[
47'file1' = > [
48'cat' = > 'Book',
49'subcat'=>'programming',
50'subcat'=>'php',
51  ],
52'file2' = > [
53'cat' = > 'Work',
54'subcat'=>'programming',
55'subcat'=>'php',
56  ],
57'file3' = > [
58'cat' = > 'Work',
59'subcat' = > 'Change of jobs',
60'subcat'=>'it Industry',
61  ],
62'file4' = > [
63'cat' = > 'Book',
64'subcat' = > 'Self-development',
65  ],
66'file5' = > [
67'cat'=>'Amazon',
68  ],
69];

最適 I would like to know which table configuration you think is the best.

Environment
MySQL 5.7
PHP 7.2

php mysql

2023-01-06 23:24

1 Answers

I think it is difficult to normalize DB because there are too few requirements.

For example, subcat is the same in programming, but since the parent cat is in a different category, can programming of subcat be treated as a different category?
Is there a limit to the depth of the hierarchy?I would like you to write down some more requirements for what you want to do.

If you can treat it as a different category and the hierarchy of the parent-child hierarchy does not deepen, I think you can do it if you have the following two tables.

The general structure of each table is as follows:

Category Table

Table to Associate Categories and Files

In the above two tables, while retrieving the list of files from DB, you will get the category ID of the lowest layer, so
"After that, if PHP recursively executes the ""Get Category Name and Parent Category ID"" function until the parent category disappears, the requested configuration will be realized."

For example, there are only three tiers, so I can't determine if there are more than four tiers, but
The above category table is a recursive structure and can theoretically be any number of tiers.
*I don't recommend it because the call of recursive functions increases as the hierarchy deepens.
Also, if you change the subsubsubcat part to subsubsubcat depending on the depth of the hierarchy, you can determine the depth of the target category by counting it during recursive calls.

After that, add columns to meet your requirements or separate tables.

Are you helping me answer this question?

2023-01-06 23:25

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656