MySQL Basic beginner learning "9" Infinite level classification table design

For example: Books/novels, literature .../four classics, drama ...

In theory, many sheets can be designed;

However, as the classification increases gradually, the number of these tables cannot be infinitely expanded;

So for the infinite class table We generally use the following form (through their own connection to achieve):

In this example, we design at least three fields: the ID of the category, the name of the category, and the ID of the parent class.

Insert Record:

  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Home appliance ', DEFAULT);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Computer, Office ', DEFAULT);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Everyone electricity ', 1);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Living electrical ', 1);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Flat screen TV ', 3);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Air conditioning ', 3);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' fan ', 4);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Water dispenser ', 4);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Computer Machine ', 2);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Computer accessories ', 2);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Notebooks ', 9);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Super Ben ', 9);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Game Ben ', 9);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' CPU ', ten);  INSERT tdb_goods_types (type_name,parent_id) VALUES (' Host ', 10);

To illustrate:

Here home appliances, computer office for the top classification, no Father node, so parent_id for 0

Everyone electricity, life appliances are the sub-category of household appliances, parent_id for 1

Flat-screen TV, air conditioning belongs to everyone--3

Electric fan, water dispenser belongs to living electrical appliances--4

etc...

Find by "Self-connect": The same data table is connected to itself. Must take the alias, otherwise cannot distinguish!!!

Imagine that there is an identical table on the right side of the table, which is the parent table and which is the child table.

For example, the right side is the child table (query table), the left side is the parent table, then the parent_id in the left table is useless, because the parent_id field in the child table points to the type_id field in the parent table

We can also look for subclasses under the subclass, parent, and parent classes:

Reference is different, now refer to the parent

The number of child classes under the parent class and the parent class:

MySQL Basic beginner learning "9" Infinite level classification table design