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 codeGoods 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 codeTiered 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 codeMerchandise 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 codeCommodity 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 codeManagement 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 codeProduct 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 codeMobile 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 codeCommodity 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 codeThe public,
Mall project full set of learning tutorials serialized, attention to the public number the first time access.
