Classification table design skills for grocery shopping system in MySQL

Classification table design skills for the food shopping system in MySQL

Introduction:
In the process of purchasing food, classification is very important. For a grocery shopping system, the design of the classification table is a very critical step. This article will introduce the techniques of designing the classification table of the grocery shopping system in MySQL and provide specific code examples.

1. Analyze requirements
Before designing the classification table, we need to analyze the requirements and determine the hierarchical structure and attributes of the classification. For a food shopping system, categories that can be considered include: ingredients, dishes, kitchen utensils, etc. These categories can be further subdivided. For example, ingredients can be divided into vegetables, fruits, meat, etc. On this basis, we can determine the fields of the classification table, such as classification ID, classification name, parent classification ID, etc.

2. Design category table

CREATE TABLE category (
category_id INT(11) NOT NULL AUTO_INCREMENT,
category_name VARCHAR (50) NOT NULL,
parent_id INT(11) DEFAULT NULL,
PRIMARY KEY (category_id),
INDEX idx_parent_id ( parent_id),
FOREIGN KEY (parent_id) REFERENCES category (category_id)
);

Description:

• The category_id field is used as the unique identifier of the category, uses the INT type, and is set as an auto-incrementing primary key.
• The category_name field is used to store the category name, using VARCHAR type, with a length of 50 characters.
• The parent_id field is used to store the ID of the parent category, using the INT type and set to be nullable. For first-level classification, parent_id is NULL.
• Set the category_id field as the primary key to facilitate quick search.
• Set the idx_parent_id index to facilitate quick query of subcategories based on the parent category ID.
• Set foreign key constraints to ensure the validity of the parent category ID.

3. Insert categorical data

INSERT INTO category (category_name, parent_id) VALUES ('ingredients', NULL);
INSERT INTO category (category_name, parent_id) VALUES ( 'Vegetables', 1);
INSERT INTO category (category_name, parent_id) VALUES ('Fruit', 1);
INSERT INTO category (category_name, parent_id) VALUES ('Meat', 1);
INSERT INTO category (category_name, parent_id) VALUES ('dishes', NULL);
INSERT INTO category (category_name, parent_id) VALUES ('Sichuan cuisine', 5);
INSERT INTO category (category_name, parent_id) VALUES ('Cantonese Cuisine', 5);
INSERT INTO category (category_name, parent_id) VALUES ('Hunan Cuisine', 5);
INSERT INTO category (category_name, parent_id) VALUES ('Kitchen Utensils', NULL);
INSERT INTO category (category_name, parent_id) VALUES ('knives', 9);
INSERT INTO category (category_name, parent_id) VALUES ('cookware', 9);
INSERT INTO category (category_name, parent_id) VALUES ('Tableware', 9);

Note:

• When inserting data of the first level category, parent_id is set to NULL.
• When inserting sub-category data, parent_id is set to the corresponding parent category ID.

4. Query category data

1. Query the list of all first-level categories and their subcategories:

SELECT p.category_name AS parent_category , c.category_name AS child_category
FROM category p
LEFT JOIN category c ON p.category_id = c.parent_id
WHERE p.parent_id IS NULL;

2. Query the specified category Subcategory list:

SELECT category_name FROM category WHERE parent_id = 1;

3. Query the parent category information of the specified category:

SELECT p .category_name AS parent_category, c.category_name AS child_category
FROM category p
JOIN category c ON p.category_id = c.parent_id
WHERE c.category_name = 'vegetable';

5. Summary
Through the above design and sample code, we can implement a grocery shopping system with classification function. Through the design of classification tables, we can flexibly manage and organize different ingredients, dishes, kitchen utensils, etc. I hope this article can provide you with some reference and help in designing classification tables in MySQL.

The above is the detailed content of Classification table design skills for grocery shopping system in MySQL. For more information, please follow other related articles on the PHP Chinese website!