1. Design category table

Category numberIt is used to distinguish which category belongs to, for example, 1000-1999 belongs to medical

CREATE TABLE t_spec_group(
  id INT UNSIGNED PRIMARYKEY AUTO_INCREMENT COMMENT "Primary KEY ", spg_idINT UNSIGNED NOT NULLCOMMENT "category id ", 'name'VARCHAR(200) NOT NULLCOMMENT "Category id ",UNIQUE INDEX unq_spg_id(spg_id),
  UNIQUE INDEX unq_name(`name`),
  INDEX idx_spg_id(spg_id)
) COMMENT "品类表"
Copy the code

UNIQUE INDEX UNIQUE INDEX

Primary keys differ from unique indexes

  • Null values are not allowed for primary keys and are allowed for unique indexes
  • Only one primary key is allowed and multiple unique indexes are allowed
  • A primary key produces a unique clustered index, and a unique index produces a unique non-clustered index

example

2. Design a parameter table

CREATE TABLE t_spec_param(
  id INT UNSIGNED PRIMARYKEY AUTO_INCREMENT COMMENT "Primary KEY ", spg_idINT UNSIGNED NOT NULLCOMMENT "category id ", spp_idINT UNSIGNED NOT NULLCOMMENT "Parameter id ", 'name'VARCHAR(200) NOT NULLCOMMENT "Parameter name ", 'numeric` BOOLEAN NOT NULLCOMMENT "Whether the parameter is a number ", unitVARCHAR(200COMMENT "unit ", genericBOOLEAN NOT NULLCOMMENT "Whether is a general parameter ", searchingBOOLEAN NOT NULLCOMMENT "Whether to use for general search ", segementsVARCHAR(200COMMENT "表", INDEX idx_spg_id(spg_id), INDEX idx_spp_id(spp_id),) COMMENT" 表"Copy the code

2. Design brand tables

CREATE TABLE t_brand(
  id INT UNSIGNED PRIMARYKEY AUTO_INCREMENT COMMENT "primary KEY ", 'name'VARCHAR(200) NOT NULLCOMMENT "Brand name ", imageVARCHAR(200COMMENT "picture url ", letterCHAR(1) NOT NULLCOMMENT "Brand name ",UNIQUEINDEX unq_name(' name '), INDEX idx_letter(letter) COMMENT"Copy the code

example

3. Commodity classification Table

CREATE TABLE t_category(
  id INT UNSIGNED PRIMARYKEY AUTO_INCREMENT COMMENT "primary KEY ", 'name'VARCHAR(200) NOT NULLCOMMENT "Category Name ", parent_idINTUNSIGNED COMMENT "Parent class ID", if_parentBOOLEAN NOT NULLCOMMENT "Does the subclass exist? ", sortINT UNSIGNED NOT NULLCOMMENT "表 示 ", INDEX idx_parent_id(parent_id), INDEX idx_sort(sort)) COMMENT" 表 示 ", INDEX idx_sort(sort)Copy the code

example

4. Design classification and brand association table

CREATE TABLE t_category_brand(
  category_id INTUNSIGNED COMMENT "category ID", brand_idINTUNSIGNED COMMENT "中 国 ID",PRIMARYCategory_id (category_id, category_id, category_id) and category_id (category_id, category_id, category_id)Copy the code

5. Design the product list

CREATE TABLE t_spu(
  id INT UNSIGNED PRIMARYKEY AUTO_INCREMENT COMMENT "Primary KEY ", titleVARCHAR(200) NOT NULLCOMMENT "title ", sub_titleVARCHAR(200) NOT NULLCOMMENT "category_id ", category_idINT UNSIGNED NOT NULLCOMMENT "Category ID", brand_idINT UNSIGNED NOT NULLCOMMENT "brand ID", spg_idINT UNSIGNED NOT NULLCOMMENT "category ID", saleableBOOLEAN NOT NULLCOMMENT "whether to list ", vaildBOOLEAN NOT NULLCOMMENT "Valid ", create_timeTIMESTAMP NOT NULL DEFAULTNOW() COMMENT "Add time ", last_update_timeTIMESTAMP NOT NULL DEFAULTCategory_id (' category_id ') CATEGORY_ID (' category_id '), category_id(' category_id '), category_id(' category_id '), category_id(' category_id '), category_id(' category_id '), category_id(' category_id ')) INDEX idx_saleable(saleable), INDEX idx_saleable(vaild)) COMMENTCopy the code

6. Design the merchandise list

CREATE TABLE t_sku(
  id INT UNSIGNED PRIMARYKEY AUTO_INCREMENT COMMENT primary KEY, spu_idINT UNSIGNED NOT NULLCOMMENT "Product ID", titleVARCHAR(200) NOT NULLCOMMENT "commodity ", images JSON COMMENT" commodity ", priceDECIMAL(10.2) UNSIGNED NOT NULLCOMMENT "price ", param JSONNOT NULLCOMMENT "parameter ", saleableBOOLEAN NOT NULLCOMMENT "whether to list ", vaildBOOLEAN NOT NULLCOMMENT "Valid ", create_timeTIMESTAMP NOT NULL DEFAULTNOW() COMMENT "Add time ", last_update_timeTIMESTAMP NOT NULL DEFAULTNOW() COMMENT "主 位 名 字 ", INDEX idx_spu_id(spu_id), INDEX idx_saleable(saleable), INDEX idx_saleable(vaild) COMMENTCopy the code

example

paramValues are set according to the parameter table