MySQL table design guide: Create a simple product classification table

MySQL Table Design Guide: Creating a Simple Product Classification Table

In database design, good table design is very important, it directly affects the data storage and query efficiency. This article will introduce how to create a simple product classification table and provide corresponding code examples.

1. Table structure design

The product classification table mainly includes the following fields: category ID, category name, and parent category ID. Among them, the category ID is the primary key of the table, the category name stores the name of the category, and the parent category ID is used to represent the parent category of the current category.

The following is an example of a DDL statement for a product classification table:

CREATE TABLE category (
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(100) NOT NULL,
  parent_category_id INT,
  FOREIGN KEY (parent_category_id) REFERENCES category(category_id)
);

When designing the table structure, we need to pay attention to the following points:

1. Primary key design: Category ID As a primary key, each category is guaranteed to have a unique identifier. In order to better support queries, you can use the auto-increment attribute (AUTO_INCREMENT) to generate classification IDs.
2. Field design: The category name field (category_name) is used to store the name of the category, using the VARCHAR type and setting an appropriate length limit. The parent category ID field (parent_category_id) is used to represent the parent category of the current category. It is also a foreign key and is associated with the category ID field of the category table.
3. Foreign key constraints: Through the FOREIGN KEY constraint, we ensure that the parent classification ID must be a valid classification ID, that is, it must exist in the classification ID of the classification table.

2. Data Insertion Example

Next, we can insert some sample data into the product classification table through the INSERT statement to verify the correctness of the table structure.

INSERT INTO category (category_name, parent_category_id) VALUES ('电子产品', NULL);
INSERT INTO category (category_name, parent_category_id) VALUES ('手机', 1);
INSERT INTO category (category_name, parent_category_id) VALUES ('平板电脑', 1);
INSERT INTO category (category_name, parent_category_id) VALUES ('家居电器', NULL);
INSERT INTO category (category_name, parent_category_id) VALUES ('冰箱', 4);
INSERT INTO category (category_name, parent_category_id) VALUES ('洗衣机', 4);

The above example data shows the hierarchical relationship of product classification, in which "electronic products" and "home appliances" are top-level categories, and "mobile phones" and "tablets" are sub-categories of "electronic products"," Refrigerators" and "Washing Machines" are subcategories of "Home Appliances".

3. Query Example

In practical applications, we usually need to query based on the category ID, such as querying all subcategories of a certain category. The following are some common query examples:

1. Query all subcategories of a certain category

SELECT * FROM category WHERE parent_category_id = 1;

The above query statement will return all subcategories of "Electronic Products", that is "Phone" and "Tablet".

2. Query the parent category of a category

SELECT parent.* FROM category child JOIN category parent
ON child.parent_category_id = parent.category_id
WHERE child.category_id = 2;

The above query statement will return the parent category of "mobile phone", which is "electronic products".

4. Summary

Through the introduction of this article, we have learned how to design a simple product classification table and provided corresponding code examples. Reasonable table structure design has an important impact on data storage and query. Therefore, in practical applications, appropriate table structure design needs to be carried out according to business needs. I hope this article can provide you with some reference and help in database table design.

The above is the detailed content of MySQL table design guide: Create a simple product classification table. For more information, please follow other related articles on the PHP Chinese website!