Table Analysis of commodity Module database (II)

SpringBoot actual combat e-commerce project mall (18K + STAR) Address: github.com/macrozheng/…

Introduction to the

In the following article, this paper mainly analyzes the tables of editing commodities, commodity evaluation and reply, and commodity operation record, adopting the form of database table and function comparison.

Edit commodity

Correlation table structure

Goods table

Commodity information mainly includes four parts: basic commodity information, promotion information, commodity attribute information, commodity association, commodity table is the basic information of the whole commodity.

create table pms_product
(
   id                   bigint not null auto_increment,
   brand_id             bigint comment 'the brand id',
   product_category_id  bigint comment 'Brand Category ID',
   feight_template_id   bigint comment 'Freight Template ID',
   product_attribute_category_id bigint comment 'Brand Attribute Category ID'.name                 varchar(64) not null comment 'Trade Name',
   pic                  varchar(255) comment 'images',
   product_sn           varchar(64) not null comment 'the article number',
   delete_status        int(1) comment 'Delete status: 0-> Not deleted; 1-> Delete ',
   publish_status       int(1) comment 'Shelf status: 0-> Down; 1 - > stores',
   new_status           int(1) comment 'New status :0-> Not new; 1 - > new products',
   recommand_status     int(1) comment 'Recommended status; 0-> Not recommended; 1 - > recommended ',
   verify_status        int(1) comment 'Audit status: 0-> Unaudited; 1-> Approved '.sort                 int comment 'order',
   sale                 int comment 'sales',
   price                decimal(10.2) comment 'price',
   promotion_price      decimal(10.2) comment 'Promotional price',
   gift_growth          int default 0 comment 'Gift growth value',
   gift_point           int default 0 comment 'Bonus points',
   use_point_limit      int comment 'Limit the number of credits used',
   sub_title            varchar(255) comment 'Subtitle',
   description          text comment 'Product Description',
   original_price       decimal(10.2) comment 'Market price',
   stock                int comment 'inventory',
   low_stock            int comment 'Inventory warning value',
   unit                 varchar(16) comment 'unit',
   weight               decimal(10.2) comment 'Weight of goods, default is grams',
   preview_status       int(1) comment 'Whether it is a trailer: 0-> No; 1 - > ',
   service_ids          varchar(64) comment 'Products and Services separated by commas: 1-> Worriable returns; 2-> Quick refund; 3-> Free shipping ',
   keywords             varchar(255) comment 'Keyword',
   note                 varchar(255) comment 'note',
   album_pics           varchar(255) comment 'Album pictures, even product pictures limited to 5, separated by commas',
   detail_title         varchar(255) comment 'Detailed Title',
   detail_desc          text comment 'Details',
   detail_html          text comment 'Product Details Webpage Content',
   detail_mobile_html   text comment 'Mobile Web Page Details',
   promotion_start_time datetime comment 'Promotion Start Time',
   promotion_end_time   datetime comment 'Promotion End Time',
   promotion_per_limit  int comment 'Activity Limit',
   promotion_type       int(1) comment 'Promotion type: 0-> No promotion using the original price; 1-> Use promotional prices; 2-> Use the membership price; 3-> Use ladder prices; 4-> Full use reduction price; 5-> Limited time purchase ',
   product_category_name varchar(255) comment 'Product Category Name',
   brand_name           varchar(255) comment 'Brand Name',
   primary key (id));Copy the code

Goods SKU table

A SKU refers to a Stock Keeping Unit, anda SPU refers to a Standard Product Unit. For example: The iPhone XS is an SPU, while the iPhone XS Public 64GB Silver is an SKU.

create table pms_sku_stock
(
   id                   bigint not null auto_increment,
   product_id           bigint comment 'commodity id',
   sku_code             varchar(64) not null comment 'sku code',
   price                decimal(10.2) comment 'price',
   stock                int default 0 comment 'inventory',
   low_stock            int comment 'Early warning stock',
   sp1                  varchar(64) comment 'Specification Attribute 1',
   sp2                  varchar(64) comment 'Specification Attribute 2',
   sp3                  varchar(64) comment 'Specification Attribute 3',
   pic                  varchar(255) comment 'Show pictures',
   sale                 int comment 'sales',
   promotion_price      decimal(10.2) comment 'Promotional price per item',
   lock_stock           int default 0 comment 'Lock inventory',
   primary key (id));Copy the code

Tiered commodity price list

Commodity discount table, the purchase of the same commodity to meet a certain number, you can use the discount price for purchase. You can get a 20% discount if you buy two items.

create table pms_product_ladder
(
   id                   bigint not null auto_increment,
   product_id           bigint comment 'commodity id'.count                int comment 'Quantity of goods satisfied',
   discount             decimal(10.2) comment 'discount',
   price                decimal(10.2) comment 'Discounted price',
   primary key (id));Copy the code

Merchandise inventory table

Commodity discount table, the purchase of goods to meet a certain amount, you can reduce a certain amount. Subtract 100 yuan from every purchase of 1000.

create table pms_product_full_reduction
(
   id                   bigint not null auto_increment,
   product_id           bigint comment 'commodity id',
   full_price           decimal(10.2) comment 'Goods satisfied Amount',
   reduce_price         decimal(10.2) comment 'Merchandise Reduction amount',
   primary key (id));Copy the code

Commodity membership price list

According to different membership levels, can be purchased at different membership prices. There is a flaw in the design here. It can be made into how many yuan or discount can be deducted or purchased by different member levels.

create table pms_member_price
(
   id                   bigint not null auto_increment,
   product_id           bigint comment 'commodity id',
   member_level_id      bigint comment 'Member Level ID',
   member_price         decimal(10.2) comment 'Membership price',
   member_level_name    varchar(100) comment 'Name of Member Level',
   primary key (id));Copy the code

Management Side Display

Fill in commodity information

Fill in merchandise promotion

specials

The member price

Ladder price

Full price reduction

Fill in commodity attributes

Select commodity association

Mobile display

The introduction

Graphic details

Related to the project

Product evaluation and reply

Correlation table structure

Commodity evaluation form

create table pms_comment
(
   id                   bigint not null auto_increment,
   product_id           bigint comment 'commodity id',
   member_nick_name     varchar(255) comment 'Member nickname',
   product_name         varchar(255) comment 'Trade Name',
   star                 int(3) comment 'Rating stars: 0->5',
   member_ip            varchar(64) comment 'IP of evaluation',
   create_time          datetime comment 'Creation time',
   show_status          int(1) comment 'Show or not',
   product_attribute    varchar(255) comment 'Attributes of goods at time of purchase',
   collect_couont       int comment 'Collection number',
   read_count           int comment 'Reading number'.content              text comment 'content',
   pics                 varchar(1000) comment 'Address to upload picture, separated by commas',
   member_icon          varchar(255) comment 'Comment user profile picture',
   replay_count         int comment 'Number of replies',
   primary key (id));Copy the code

Product evaluation response form

create table pms_comment_replay
(
   id                   bigint not null auto_increment,
   comment_id           bigint comment 'comment id',
   member_nick_name     varchar(255) comment 'Member nickname',
   member_icon          varchar(255) comment 'Member profile picture'.content              varchar(1000) comment 'content',
   create_time          datetime comment 'Creation time'.type                 int(1) comment 'Type of reviewer; 0 - > member; 1-> Admin ',
   primary key (id));Copy the code

Mobile display

List of product evaluation

Commodity Evaluation Details

Product Reply List

Product audit and operation records

Correlation table structure

Commodity examination record form

Used to record commodity audit records

create table pms_product_vertify_record
(
   id                   bigint not null auto_increment,
   product_id           bigint comment 'commodity id',
   create_time          datetime comment 'Creation time',
   vertify_man          varchar(64) comment 'Examiner'.status               int(1) comment 'Status after review: 0-> failed; 2-> Passed ',
   detail               varchar(255) comment 'Feedback details',
   primary key (id));Copy the code

Commodity operation record form

Used to record commodity operation records

create table pms_product_operate_log
(
   id                   bigint not null auto_increment,
   product_id           bigint comment 'commodity id',
   price_old            decimal(10.2) comment 'Price before change',
   price_new            decimal(10.2) comment 'Changed price',
   sale_price_old       decimal(10.2) comment 'Prior to change',
   sale_price_new       decimal(10.2) comment 'Changed price',
   gift_point_old       int comment Integral before change,
   gift_point_new       int comment Integral after change,
   use_point_limit_old  int comment 'Change the pre-credits usage limit',
   use_point_limit_new  int comment 'Limit on use of credits after change',
   operate_man          varchar(64) comment 'Operator',
   create_time          datetime comment 'Creation time',
   primary key (id));Copy the code

The public,

Mall project full set of learning tutorials serialized, attention to the public number the first time access.