文章内容
2018/3/5 10:53:08,作 者: 黄兵
两种商品数据库的MySQL代码
最近在研究商品数据库,世界上的商品千千万万,每种商品又有自己的属性,例如:冲锋衣有不同品牌,同一品牌又有不同种颜色,同一颜色又有不同大小。
感觉这些无穷无尽,所以必须要一个健壮的数据库来承载这些数据。
最近几天研究了一下商品数据库的设计,收益良多,将最近收集的MySQL建库SQL代码记录下来,不断学习,不断进步。
第一种:
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;
这个表看的不是很明白,等再继续研究一段时间再回来看看这个库是如何设计的。哪位大牛看的明白欢迎在下面留言。
第二种:
/* Navicat MySQL Data Transfer Source Server : LocalHost Source Server Version : 50537 Source Host : localhost:3306 Source Database : sku1 Target Server Type : MYSQL Target Server Version : 50537 File Encoding : 65001 Date: 2017-07-07 17:12:41 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for brand -- ---------------------------- DROP TABLE IF EXISTS `brand`; CREATE TABLE `brand` ( `b_id` int(12) NOT NULL AUTO_INCREMENT, `b_name` varchar(50) DEFAULT NULL, `i_id` int(12) DEFAULT NULL, PRIMARY KEY (`b_id`), KEY `i_rel_bid` (`i_id`), KEY `b_name` (`b_name`), CONSTRAINT `i_rel_bid` FOREIGN KEY (`i_id`) REFERENCES `item` (`i_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of brand -- ---------------------------- INSERT INTO `brand` VALUES ('1', '李宁', '2'); INSERT INTO `brand` VALUES ('2', '耐克', '2'); INSERT INTO `brand` VALUES ('3', '李宁', '4'); INSERT INTO `brand` VALUES ('4', '耐克', '4'); INSERT INTO `brand` VALUES ('5', '红蜻蜓', '1'); INSERT INTO `brand` VALUES ('6', '东方骆驼', '4'); INSERT INTO `brand` VALUES ('7', '婷美', '4'); INSERT INTO `brand` VALUES ('8', 'Chanel', '5'); INSERT INTO `brand` VALUES ('9', 'CoCo', '7'); INSERT INTO `brand` VALUES ('10', 'Amani', '8'); -- ---------------------------- -- Table structure for item -- ---------------------------- DROP TABLE IF EXISTS `item`; CREATE TABLE `item` ( `i_id` int(12) NOT NULL AUTO_INCREMENT, `i_name` varchar(50) DEFAULT NULL, `i_parent_id` int(12) DEFAULT NULL, PRIMARY KEY (`i_id`), KEY `i_fk_i` (`i_parent_id`), CONSTRAINT `i_fk_i` FOREIGN KEY (`i_parent_id`) REFERENCES `item` (`i_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of item -- ---------------------------- INSERT INTO `item` VALUES ('1', '服装/鞋包', null); INSERT INTO `item` VALUES ('2', '男装', '1'); INSERT INTO `item` VALUES ('3', '流行男鞋', '1'); INSERT INTO `item` VALUES ('4', '女装', '1'); INSERT INTO `item` VALUES ('5', '箱包', null); INSERT INTO `item` VALUES ('6', '双肩包', '5'); INSERT INTO `item` VALUES ('7', '单肩包', '5'); INSERT INTO `item` VALUES ('8', '行李箱', '5'); INSERT INTO `item` VALUES ('9', '其他', null); -- ---------------------------- -- Table structure for product -- ---------------------------- DROP TABLE IF EXISTS `product`; CREATE TABLE `product` ( `p_id` int(12) NOT NULL AUTO_INCREMENT, `p_name` varchar(50) DEFAULT NULL, `b_name` varchar(50) DEFAULT NULL, `p_fk_p` int(12) DEFAULT NULL, `p_fk_i` int(12) DEFAULT NULL, PRIMARY KEY (`p_id`), KEY `bname_rel_p` (`b_name`), KEY `p_rel_p` (`p_fk_p`), KEY `i_rel_p` (`p_fk_i`), CONSTRAINT `i_rel_p` FOREIGN KEY (`p_fk_i`) REFERENCES `item` (`i_id`), CONSTRAINT `bname_rel_p` FOREIGN KEY (`b_name`) REFERENCES `brand` (`b_name`), CONSTRAINT `p_rel_p` FOREIGN KEY (`p_fk_p`) REFERENCES `product` (`p_id`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of product -- ---------------------------- INSERT INTO `product` VALUES ('1', '球鞋', '李宁', null, '1'); INSERT INTO `product` VALUES ('2', '网球', '李宁', null, '9'); INSERT INTO `product` VALUES ('3', '衬衫', '李宁', null, '1'); INSERT INTO `product` VALUES ('4', '袜子', '李宁', null, '1'); INSERT INTO `product` VALUES ('5', '球鞋', '耐克', null, '1'); INSERT INTO `product` VALUES ('6', 'Air', '耐克', null, '1'); INSERT INTO `product` VALUES ('7', '袜子', '耐克', null, '1'); INSERT INTO `product` VALUES ('8', '乒乓球', '耐克', null, '9'); INSERT INTO `product` VALUES ('9', '高跟鞋', '红蜻蜓', null, '1'); INSERT INTO `product` VALUES ('10', '水晶鞋', '红蜻蜓', null, '1'); INSERT INTO `product` VALUES ('11', '凉鞋', '婷美', null, '1'); INSERT INTO `product` VALUES ('12', '皮鞋', '婷美', null, '1'); INSERT INTO `product` VALUES ('13', '高跟鞋', '婷美', null, '1'); INSERT INTO `product` VALUES ('14', 'Air1', '耐克', '6', '1'); INSERT INTO `product` VALUES ('15', 'Air2', '耐克', '6', '1'); INSERT INTO `product` VALUES ('16', 'Air3', '耐克', '6', '1'); -- ---------------------------- -- Table structure for product_pro -- ---------------------------- DROP TABLE IF EXISTS `product_pro`; CREATE TABLE `product_pro` ( `pp_id` int(12) NOT NULL AUTO_INCREMENT, `pp_fk_p` int(12) DEFAULT NULL, `pp_fk_pv` int(12) DEFAULT NULL, `pp_fk_pn` int(12) DEFAULT NULL, PRIMARY KEY (`pp_id`), KEY `pv_rel_pp` (`pp_fk_pv`), KEY `p_rel_pp` (`pp_fk_p`), KEY `pn_rel_pp` (`pp_fk_pn`), CONSTRAINT `pn_rel_pp` FOREIGN KEY (`pp_fk_pn`) REFERENCES `pro_name` (`pro_id`), CONSTRAINT `pv_rel_pp` FOREIGN KEY (`pp_fk_pv`) REFERENCES `pro_value` (`pv_id`), CONSTRAINT `p_rel_pp` FOREIGN KEY (`pp_fk_p`) REFERENCES `product` (`p_id`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of product_pro -- ---------------------------- INSERT INTO `product_pro` VALUES ('9', '1', '1', '1'); INSERT INTO `product_pro` VALUES ('10', '1', '2', '1'); INSERT INTO `product_pro` VALUES ('11', '1', '3', '2'); INSERT INTO `product_pro` VALUES ('12', '1', '4', '2'); INSERT INTO `product_pro` VALUES ('13', '1', '5', '2'); INSERT INTO `product_pro` VALUES ('14', '1', '6', '3'); INSERT INTO `product_pro` VALUES ('15', '1', '7', '3'); INSERT INTO `product_pro` VALUES ('16', '1', '8', '3'); -- ---------------------------- -- Table structure for product_sku -- ---------------------------- DROP TABLE IF EXISTS `product_sku`; CREATE TABLE `product_sku` ( `ps_id` int(12) NOT NULL AUTO_INCREMENT, `pd_fk_id` int(12) DEFAULT NULL, `pd_num` int(11) DEFAULT NULL, `pd_price` decimal(10,4) DEFAULT NULL, `pd_name` varchar(50) DEFAULT NULL, `pd_properties` varchar(300) DEFAULT NULL, PRIMARY KEY (`ps_id`), KEY `p_rel_ps` (`pd_fk_id`), CONSTRAINT `p_rel_ps` FOREIGN KEY (`pd_fk_id`) REFERENCES `product` (`p_id`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of product_sku -- ---------------------------- INSERT INTO `product_sku` VALUES ('1', '1', '53', '528.5000', '李宁-A79-12球鞋(红,亚麻,北美)', '1;3;6;'); INSERT INTO `product_sku` VALUES ('2', '1', '23', '238.9000', '李宁-A79-12球鞋(绿,塑料,中国)', '2;4;8;'); INSERT INTO `product_sku` VALUES ('3', '1', '10', '370.0000', '李宁-A21-11球鞋(绿,针织,南非)', '2;5;7;'); INSERT INTO `product_sku` VALUES ('4', '2', '12', '123.3000', '李宁-B1-12网球(红,针织,南非)', '1;5;7;'); INSERT INTO `product_sku` VALUES ('5', '2', '19', '250.0000', '李宁-B3-18网球(绿,针织,南非)', '2;5;7;'); INSERT INTO `product_sku` VALUES ('6', '3', '12', '200.0000', '李宁-A-12衬衫(绿,针织,南非)', '2;5;7;'); INSERT INTO `product_sku` VALUES ('7', '4', '10', '10.0000', '李宁-C-12袜子(绿,针织,南非)', '2;5;7;'); INSERT INTO `product_sku` VALUES ('9', '5', '5', '498.5000', '耐克-A79-12球鞋(绿,亚麻,北美)', '2;3;6;'); INSERT INTO `product_sku` VALUES ('10', '5', '25', '498.5000', '耐克-A79-12球鞋(绿,塑料,北美)', '2;4;6;'); INSERT INTO `product_sku` VALUES ('11', '5', '20', '498.5000', '耐克-A79-12球鞋(绿,塑料,南非)', '2;4;7;'); INSERT INTO `product_sku` VALUES ('13', '14', '1', '238.9000', '耐克Air1-A102-23球鞋(绿,亚麻,中国)', '2;3;8;'); INSERT INTO `product_sku` VALUES ('14', '14', '2', '250.9000', '耐克Air1-A102-23球鞋(绿,针织,北美)', '2;5;6;'); INSERT INTO `product_sku` VALUES ('15', '15', '22', '200.9000', '耐克Air2-A102-23球鞋(绿,针织,南非)', '2;5;7;'); INSERT INTO `product_sku` VALUES ('16', '16', '12', '200.9000', '耐克Air3-A21-11球鞋(红,亚麻,北美)', '1;3;6;'); INSERT INTO `product_sku` VALUES ('17', '12', '12', '200.9000', '婷美-A21-11凉鞋(红,亚麻,南非)', '1;3;7;'); INSERT INTO `product_sku` VALUES ('18', '12', '12', '238.9000', '婷美-A21-11凉鞋(红,亚麻,中国)', '1;3;8;'); INSERT INTO `product_sku` VALUES ('19', '12', '12', '320.9000', '婷美-A21-11凉鞋(红,塑料,南非)', '2;4;8;'); -- ---------------------------- -- Table structure for pro_name -- ---------------------------- DROP TABLE IF EXISTS `pro_name`; CREATE TABLE `pro_name` ( `pro_id` int(12) NOT NULL AUTO_INCREMENT, `pro_name` varchar(50) DEFAULT NULL, `pro_fk_iid` int(12) DEFAULT NULL, `pro_has_otherName` char(2) NOT NULL DEFAULT '0', `pro_has_color` char(2) NOT NULL DEFAULT '0', `pro_has_enum` char(2) NOT NULL DEFAULT '0', `pro_has_input` char(2) NOT NULL DEFAULT '0', `pro_is_key` char(2) NOT NULL DEFAULT '0', `pro_is_sale` char(2) NOT NULL DEFAULT '0', `pro_is_must` char(2) NOT NULL DEFAULT '0', PRIMARY KEY (`pro_id`), KEY `i_ref_pn` (`pro_fk_iid`), CONSTRAINT `i_ref_pn` FOREIGN KEY (`pro_fk_iid`) REFERENCES `item` (`i_id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of pro_name -- ---------------------------- INSERT INTO `pro_name` VALUES ('1', '颜色', '1', '0', '1', '0', '0', '0', '0', '0'); INSERT INTO `pro_name` VALUES ('2', '材质', '1', '0', '0', '0', '0', '0', '0', '0'); INSERT INTO `pro_name` VALUES ('3', '厂商', '1', '0', '0', '0', '0', '0', '0', '0'); INSERT INTO `pro_name` VALUES ('4', '尺码', '1', '0', '0', '0', '0', '0', '0', '0'); INSERT INTO `pro_name` VALUES ('5', '面值', '5', '0', '0', '0', '0', '0', '0', '0'); INSERT INTO `pro_name` VALUES ('6', '渠道', '5', '0', '0', '0', '0', '0', '0', '0'); INSERT INTO `pro_name` VALUES ('15', '鞋跟', '3', '0', '0', '0', '0', '0', '0', '0'); -- ---------------------------- -- Table structure for pro_value -- ---------------------------- DROP TABLE IF EXISTS `pro_value`; CREATE TABLE `pro_value` ( `pv_id` int(12) NOT NULL AUTO_INCREMENT, `pv_name` varchar(50) DEFAULT NULL, `pv_fk_pid` int(12) DEFAULT NULL, PRIMARY KEY (`pv_id`), KEY `pn_rel_pv` (`pv_fk_pid`), CONSTRAINT `pn_rel_pv` FOREIGN KEY (`pv_fk_pid`) REFERENCES `pro_name` (`pro_id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of pro_value -- ---------------------------- INSERT INTO `pro_value` VALUES ('1', '红', '1'); INSERT INTO `pro_value` VALUES ('2', '绿', '1'); INSERT INTO `pro_value` VALUES ('3', '亚麻', '2'); INSERT INTO `pro_value` VALUES ('4', '塑料', '2'); INSERT INTO `pro_value` VALUES ('5', '针织', '2'); INSERT INTO `pro_value` VALUES ('6', '北美工厂店', '3'); INSERT INTO `pro_value` VALUES ('7', '南非工厂店', '3'); INSERT INTO `pro_value` VALUES ('8', '中国制造', '3'); INSERT INTO `pro_value` VALUES ('9', '高脚鞋跟', '15'); INSERT INTO `pro_value` VALUES ('10', '平底鞋跟', '15'); INSERT INTO `pro_value` VALUES ('11', '尖顶鞋跟', '15');
这张表因为有插入的数据,可以比较清楚的表示出每个表的内容。具体深入,还要在业务中不断学习,才能完全看懂这个表,并根据业务来扩展这张表。
其它相关推荐:
5、国家省市数据库
黄兵个人博客原创。
转载请注明出处:黄兵个人博客 - 两种商品数据库的MySQL代码
评论列表