今天就来巴拉主要的五张表供大家参考,有不足的地方可以互相讨论。先上一张图,然后细说
1.商品表
几乎大多数表都围绕商品表来转。
商品表存什么? 存商品的名称,分类,这些可用通用的字段,价格,库存,规格属性什么的单拉出来,降低表与表之间的耦合性。
2.规格表
存放规格名称,与商品表关联,一对多关系(一个商品对应多个规格)
3.规格值表
存放规格的具体值。与商品表,规格表,sku表关联,一对多关系,多对多关系
4.商品组合表
存放sku,spec,item的id,一对多,多对多关系
5.库存,价格表
存放库存,价格
整体关系图可以看以下两张图:
参考表:
1 对于商品模块,统一使用goods_做前缀。
2 首先是两个比较简单的表:分类表和品牌表
3
4 分类表:
5 CREATE TABLE `goods_category` (
6 `id` bigint(20) unsigned NOT NULL auto_increment,
7 `category_name` varchar(50) NOT NULL COMMENT '分类名称',
8 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
9 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
10 PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分类表';
12
13 品牌表:
14 CREATE TABLE `goods_brand` (
15 `id` bigint(20) unsigned NOT NULL auto_increment,
16 `brand_name` varchar(50) NOT NULL COMMENT '品牌名称',
17 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
18 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
19 PRIMARY KEY (`id`)
20 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='品牌表';
21
22 接下来是SPU表:
23
24 CREATE TABLE `goods_spu` (
25 `id` bigint(20) unsigned NOT NULL auto_increment,
26 `spu_no` varchar(50) NOT NULL COMMENT '商品编号,唯一',
27 `goods_name` varchar(50) NOT NULL COMMENT '商品名称',
28 `low_price` decimal(9,2) NOT NULL COMMENT '最低售价',
29 `category_id` bigint(20) NOT NULL COMMENT '分类id',
30 `brand_id` bigint(20) NOT NULL COMMENT '品牌id',
31 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
32 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
33 PRIMARY KEY (`id`),
34 UNIQUE KEY `uk_spu_no` (`spu_no`)
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='spu表';
36
37 然后是SPU规格相关的表:
38 规格表:
39 CREATE TABLE `goods_spec` (
40 `id` bigint(20) unsigned NOT NULL auto_increment,
41 `spec_no` varchar(50) NOT NULL COMMENT '规格编号',
42 `spec_name` varchar(50) NOT NULL COMMENT '规格名称',
43 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
44 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
45 PRIMARY KEY (`id`)
46 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='规格表';
47
48 规格值表:
49 CREATE TABLE `goods_spec_value` (
50 `id` bigint(20) unsigned NOT NULL auto_increment,
51 `spec_id` bigint(20) NOT NULL COMMENT '规格id',
52 `spec_value` varchar(50) NOT NULL COMMENT '规格值',
53 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
54 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
55 PRIMARY KEY (`id`)
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='规格值表';
57
58 SPU规格表
59 CREATE TABLE `goods_spu_spec` (
60 `id` bigint(20) unsigned NOT NULL auto_increment,
61 `spu_id` bigint(20) NOT NULL COMMENT 'spu_id',
62 `spec_id` bigint(20) NOT NULL COMMENT 'spec_id',
63 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
64 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
65 PRIMARY KEY (`id`)
66 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='spu规格表';
67
68 接下来是SKU相关的表:
69 SKU表:
70 CREATE TABLE `goods_sku` (
71 `id` bigint(20) unsigned NOT NULL auto_increment,
72 `sku_no` varchar(50) NOT NULL COMMENT 'sku编号,唯一',
73 `sku_name` varchar(50) NOT NULL COMMENT 'sku名称(冗余spu_name)',
74 `price` decimal(9,2) NOT NULL COMMENT '售价',
75 `stock` int(11) NOT NULL COMMENT '库存',
76 `shop_id` bigint(20) NOT NULL COMMENT '商铺id,为0表示自营',
77 `spu_id` bigint(20) NOT NULL COMMENT 'spu_id',
78 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
79 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
80 PRIMARY KEY (`id`)
81 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='sku表';
82
83 商铺表:
84 CREATE TABLE `shop_info` (
85 `id` bigint(20) unsigned NOT NULL auto_increment,
86 `shop_name` varchar(50) NOT NULL COMMENT '店铺名称',
87 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
88 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
89 PRIMARY KEY (`id`)
90 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='店铺表';
91
92 增值保障表:
93 CREATE TABLE `goods_safeguard` (
94 `id` bigint(20) unsigned NOT NULL auto_increment,
95 `safeguard_name` varchar(50) NOT NULL COMMENT '保障名称',
96 `price` decimal(9,2) NOT NULL COMMENT '保障价格',
97 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
98 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
99 PRIMARY KEY (`id`)
100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='增值保障';
101
102 SKU增值保障
103 CREATE TABLE `goods_sku_safeguard` (
104 `id` bigint(20) unsigned NOT NULL auto_increment,
105 `sku_id` bigint(20) NOT NULL COMMENT 'sku_id',
106 `safeguard_id` bigint(20) NOT NULL COMMENT 'safeguard_id',
107 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
108 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
109 PRIMARY KEY (`id`)
110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='sku增值保障';
111
112 SKU规格值表
113 CREATE TABLE `goods_sku_spec_value` (
114 `id` bigint(20) unsigned NOT NULL auto_increment,
115 `spu_id` bigint(20) NOT NULL COMMENT 'sku_id',
116 `spec_value_id` bigint(20) NOT NULL COMMENT '规格值id',
117 `gmt_create` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
118 `gmt_update` timestamp NOT NULL default '0000-00-00 00:00:00',
119 PRIMARY KEY (`id`)
120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='sku规格值';
DROP TABLE IF EXISTS `category`; -- 类目表
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`parent_flag` tinyint(1) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`use_flag` tinyint(1) DEFAULT NULL,
`code` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `category_property`; -- 类目属性关联表(公共属性,叶子类目的特有属性,设计成父子关系,不好理解,但却可以减少表的很多数据量)
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `category_property` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) DEFAULT NULL,
`property_id` int(11) DEFAULT NULL,
`parent_flag` tinyint(1) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `cat_prop_val`; -- 类目属性值表(关系表,可以精准控制每个类目的属性值)
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cat_prop_val` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cat_prop_id` int(11) DEFAULT NULL,
`value_id` int(11) DEFAULT NULL,
`status` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `goods`; -- 简单的商品表
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `goods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`category_id` int(11) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`use_flag` tinyint(1) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`up_time` datetime DEFAULT NULL,
`down_time` datetime DEFAULT NULL,
`description` varchar(45) DEFAULT NULL,
`keywords` varchar(45) DEFAULT NULL,
`min_price` varchar(45) DEFAULT NULL,
`max_price` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `goods_property`; -- 商品非销售属性表
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `goods_property` (
`id` int(11) NOT NULL,
`goods_id` int(11) DEFAULT NULL,
`property_id` int(11) DEFAULT NULL,
`property_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `goods_prop_value`;-- 商品非销售属性的值表
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `goods_prop_value` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`goods_prop_id` int(11) DEFAULT NULL,
`value_id` int(11) DEFAULT NULL,
`value_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `goods_sku`; -- 商品sku表(同商品属性表,分开设计是可以提高查询速度)
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `goods_sku` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`goods_id` int(11) DEFAULT NULL,
`property_id` int(11) DEFAULT NULL,
`property_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `goods_sku_value`; -- 商品sku 值表
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `goods_sku_value` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`goods_sku_id` int(11) DEFAULT NULL,
`value_id` int(11) DEFAULT NULL,
`value_name` varchar(64) DEFAULT NULL,
`image_uri` varchar(128) DEFAULT NULL,
`quality` int(11) DEFAULT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `property`; -- 类目属性表
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `property` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`is_key` tinyint(1) DEFAULT NULL,
`is_multi` tinyint(1) DEFAULT NULL,
`is_color` tinyint(1) DEFAULT NULL,
`is_enum` tinyint(1) DEFAULT NULL,
`is_required` tinyint(1) DEFAULT NULL,
`is_alias` tinyint(1) DEFAULT NULL,
`is_sell` tinyint(1) DEFAULT NULL,
`is_input` tinyint(1) DEFAULT NULL,
`is_search` tinyint(1) DEFAULT NULL,
`is_parent` tinyint(1) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`use_flag` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `value`; -- 属性值表
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `value` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`use_flag` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;