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;
求高人指点更好的设计!!!!!!