php - 具有第一层子类别和第二层子类别的类别的 MySQL 规范化

Question

我正在学习网页设计的数据库规范化，但找不到我需要的答案。我能够从网上找到的一些教程开始，但找不到我能理解的答案。

我有几个产品类别，例如:电子产品、电器。 (我们称它们为“类别”)。

电子产品本身有子类别:手机、计算机、平板电脑。 (“第一层类别”)

然后手机有自己的类别:智能手机、平板电脑等(“第二层类别”)

所以目前我有这样的表:

Categories table
------------------------------------------
|    category_id     |   category_name   |
------------------------------------------
|          1         |     Electronics   |
|          2         |     Appliances    |
------------------------------------------

1st tier categories table
------------------------------------------
|    category_id     |   1st_tier_name   |
------------------------------------------
|          1         |     Cell phones   |
|          1         |      Computers    |
|          1         |       Tablets     |
------------------------------------------

这是我卡住的地方。如何创建第二层类别表以及如何创建在这种情况下有效的链接表？谢谢你们的宝贵时间。

Answer 1

使用 parent_id 指定记录属于哪个类别:

+--------+---------------+-----------+
| cat_id | name          | parent_id |
+--------+---------------+-----------+
|      1 | Electronics   |         0 |
|      2 | Appliances    |         0 |
|      3 | Cell phones   |         1 |
|      4 | Computers     |         1 |
|      5 | Tablets       |         1 |
|      6 | Smartphones   |         3 |
|      7 | Tablet Phones |         3 |
+--------+---------------+-----------+

要选择所需的数据，您可以改进如下查询:

mysql> SELECT
    -> a.name AS main_category,
    -> b.name AS second_level_category,
    -> c.name AS thrid_level_category
    ->  FROM categories AS a
    -> LEFT JOIN categories AS b ON (a.cat_id=b.parent_id)
    -> LEFT JOIN categories AS c ON (b.cat_id=c.parent_id)
    -> WHERE a.parent_id=0;
+---------------+-----------------------+----------------------+
| main_category | second_level_category | thrid_level_category |
+---------------+-----------------------+----------------------+
| Electronics   | Cell phones           | Smartphones          |
| Electronics   | Cell phones           | Tablet Phones        |
| Electronics   | Computers             | NULL                 |
| Electronics   | Tablets               | NULL                 |
| Appliances    | NULL                  | NULL                 |
+---------------+-----------------------+----------------------+

@最后评论:

mysql> desc categories;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| cat_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(50) | YES  |     | NULL    |                |
| parent_id | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show create table categories\G
*************************** 1. row ***************************
       Table: categories
Create Table: CREATE TABLE `categories` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `parent_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)