Sony AK Knowledge Center

Somebody asking about how to create table structure to store multilevel category data. Here is the scenario. For example you will create an online article system and each article will have specified category. So how about the table structure to handle this?

First you create the table for store the category, let’s say the table name is tbl_category. The structure is like this.

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| category_name | varchar(100) | YES  |     | NULL    |                |
| parent_id     | int(11)      | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Now we will explain the field one-by-one.

- Field id, is used to store the table primary key, it’s auto-increment value.
- Field category_name, is used to store the name of the category.
- Field parent_id, is used to store the parent id of specified category name, with this field you can store multilevel category data on single table.

Example data of tbl_category is like below.

mysql> select * from tbl_category;
+----+---------------+-----------+
| id | category_name | parent_id |
+----+---------------+-----------+
|  1 | PHP           |      NULL |
|  2 | Animal        |      NULL |
|  3 | Frameworks    |         1 |
|  4 | CakePHP       |         3 |
|  5 | CodeIgniter   |         3 |
|  6 | Horse         |         2 |
|  7 | Dog           |         2 |
|  8 | Dalmatians    |         7 |
+----+---------------+-----------+
8 rows in set (0.00 sec)

Above data represents category tree like below.

+- PHP
|-- Frameworks
|--- CakePHP
|--- CodeIgniter
+- Animal
|-- Horse
|-- Dog
|--- Dalmatians

It means category name PHP and Animal is the root category. PHP has Frameworks sub-category and Frameworks has two sub-category again, CakePHP and CodeIgniter. Animal has two sub-category, Horse and Dog. Dog has one sub-category called Dalmatians. All those data can be stored on single table structure like tbl_category above.

Now suppose you have table article called tbl_article, the structure is like below.

mysql> desc tbl_article;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100)  | YES  |     | NULL    |                |
| content     | varchar(1024) | YES  |     | NULL    |                |
| category_id | int(11)       | YES  |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+
4 rows in set (0.06 sec)

You can fill category_id with id field from tbl_category. The example is like below.

mysql> select * from tbl_article\G
*************************** 1. row ***************************
         id: 1
      title: CakePHP is easy framework
    content: CakePHP is very easy framework in the world.
category_id: 4
*************************** 2. row ***************************
         id: 2
      title: Dalmatians dog killed by pedestrian
    content: A big dalmatians dog killed by pedestrian on Downing Street, London.
category_id: 8
2 rows in set (0.00 sec)

We can see that article with ID 1 has category_id 4, it means it’s category is CakePHP. Article ID 2 has category_id 8, it means it’s category is Dalmatians.