Analysis of Commodity Module Database Table

Keywords: MySQL Attribute Mobile SpringBoot github

SpringBoot e-commerce project mall (20k+star) address: https://github.com/macrozheng/mall

abstract

This paper mainly analyses the tables of three functions: commodity classification, brand management and commodity types, and adopts the form of comparison between functions and table structure. Table parsing can only annotate some fields that need to be understood. Simple fields should be annotated by referring to the table.

Commodity classification

Catalogue of Commodities

create table pms_product_category
(
   id                   bigint not null auto_increment,
   parent_id            bigint comment 'Number of superior classification: 0 denotes primary classification',
   name                 varchar(64) comment 'Name',
   level                int(1) comment 'Classification level: 0->1 Class 1->2 level',
   product_count        int comment 'Quantity of commodities',
   product_unit         varchar(64) comment 'Commodity unit',
   nav_status           int(1) comment 'Is it displayed in the navigation bar: 0->No display; 1->display',
   show_status          int(1) comment 'Display status: 0->No display; 1->display',
   sort                 int comment 'sort',
   icon                 varchar(255) comment 'Icon',
   keywords             varchar(255) comment 'Keyword',
   description          text comment 'describe',
   primary key (id)
);

Management side presentation

Category List of Commodities

Adding Classification of Commodities

Mobile End Display

Brand management

Commodity Brand List

create table pms_brand
(
   id                   bigint not null auto_increment,
   name                 varchar(64) comment 'Name',
   first_letter         varchar(8) comment 'Initials',
   sort                 int comment 'sort',
   factory_status       int(1) comment 'Is it a brand manufacturer:0->No; 1->yes',
   show_status          int(1) comment 'Whether to display',
   product_count        int comment 'Product quantity',
   product_comment_count int comment 'Number of product reviews',
   logo                 varchar(255) comment 'brand logo',
   big_pic              varchar(255) comment 'District Map',
   brand_story          text comment 'Brand story',
   primary key (id)
);

Management side presentation

Brand list

Add brand

Mobile End Display

Commodity type

Commodity types are commodity attributes, which mainly refer to the specifications and parameters of commodities. Specifications are used to select when users purchase commodities, and parameters are used to mark commodity attributes and screen when searching.

Relevant table structure

Classification Table of Commodity Attributes

create table pms_product_attribute_category
(
   id                   bigint not null auto_increment,
   name                 varchar(64) comment 'Name',
   attribute_count      int comment 'Attribute quantity',
   param_count          int comment 'Parameter quantity',
   primary key (id)
);

Commodity Attribute Table

The type field is used to control whether it is a specification or a parameter

create table pms_product_attribute
(
   id                   bigint not null auto_increment,
   product_attribute_category_id bigint comment 'Classification of commodity attributes id',
   name                 varchar(64) comment 'Name',
   select_type          int(1) comment 'Property selection type: 0->Only; 1->Single election; 2->Multiple selection; corresponding attributes and parameters have different meanings;',
   input_type           int(1) comment 'Attribute input mode: 0->Manual input; 1.->Select from the list',
   input_list           varchar(255) comment 'A list of optional values separated by commas',
   sort                 int comment 'Sort field: the highest uploadable image',
   filter_type          int(1) comment 'Classification and Screening Styles: 1->Ordinary; 1->colour',
   search_type          int(1) comment 'Retrieval type; 0->No need to search;->Keyword retrieval; 2.->Range search',
   related_status       int(1) comment 'Whether the products with the same attributes are related or not;->Not related;->Relation',
   hand_add_status      int(1) comment 'Whether to support manual addition or not;->No support; 1.->Support',
   type                 int(1) comment 'Type of attribute; 0->Specifications; 1->parameter',
   primary key (id)
);

Value table of commodity attributes

If the corresponding parameters are specifications and specifications support manual addition, the table is used to store manually added values; if the corresponding commodity properties are parameters, the table is used to store the values of parameters.

create table pms_product_attribute_value
(
   id                   bigint not null auto_increment,
   product_id           bigint comment 'commodity id',
   product_attribute_id bigint comment 'Commodity attribute id',
   value                varchar(64) comment 'Manually add values of specifications or parameters, single values of parameters, separated by commas when specifications have more than one',
   primary key (id)
);

Relational Table of Commodity Classification and Attributes

Used to generate filter attributes when searching after sorting.

create table pms_product_category_attribute_relation
(
   id                   bigint not null auto_increment,
   product_category_id  bigint comment 'Commodity classification id',
   product_attribute_id bigint comment 'Commodity attribute id',
   primary key (id)
);