How to design the product table structure of the mall in MySQL?

How to design the product table structure of the mall in MySQL?

MySQL is a commonly used relational database management system that is widely used in various types of websites and applications. When designing the product table structure of the mall, factors such as product attributes, classification, and inventory need to be taken into consideration. The following will introduce in detail how to design the product table structure of the mall in MySQL and give specific code examples.

1. Basic information of the product table:
   When designing the structure of the product table, you first need to determine the basic information of the product, such as product name, price, description, pictures, etc. You can use the following code to create a product table:

CREATE TABLE IF NOT EXISTS `product` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `description` TEXT,
  `image` VARCHAR(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

2. Product classification table:
   Product classification is an important part of the classification management of products in the mall. You can use the following code to create a product classification table:

CREATE TABLE IF NOT EXISTS `category` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

3. Relationship table between products and categories:
   There is a many-to-many relationship between products and categories, and each product can belong to Multiple categories, and each category can also contain multiple products. In order to realize this relationship, you can use the following code to create a relationship table:

CREATE TABLE IF NOT EXISTS `product_category` (
  `product_id` INT(11) NOT NULL,
  `category_id` INT(11) NOT NULL,
  PRIMARY KEY (`product_id`, `category_id`),
  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

4. Product inventory table:
   Product inventory information is an essential part of the mall. You can use the following code to create a product inventory table:

CREATE TABLE IF NOT EXISTS `stock` (
  `product_id` INT(11) NOT NULL,
  `quantity` INT(11) NOT NULL,
  PRIMARY KEY (`product_id`),
  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

The product_id column in the above code is the primary key of the product table and is related to the inventory table through foreign keys.

5. Create index:
   In order to improve query efficiency, you can add appropriate indexes to the table. According to the actual situation, you can add indexes to the name and price columns of the product table, add indexes to the name column of the classification table, and add indexes to the product_id of the inventory table. Add index to column.

ALTER TABLE `product` ADD INDEX `idx_product_name` (`name`);
ALTER TABLE `product` ADD INDEX `idx_product_price` (`price`);
ALTER TABLE `category` ADD INDEX `idx_category_name` (`name`);
ALTER TABLE `stock` ADD INDEX `idx_stock_product_id` (`product_id`);

The above are the steps and code examples for designing the product table structure of the mall in MySQL. In practical applications, appropriate adjustments and optimizations can also be made according to specific needs. At the same time, you can also add other tables and fields according to business needs, such as product reviews, product attributes, etc.

The above is the detailed content of How to design the product table structure of the mall in MySQL?. For more information, please follow other related articles on the PHP Chinese website!