商品数据库
1.商品主表
CREATE TABLE `wl_goods`(
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '分类id',
`goods_sn` varchar(60) NOT NULL DEFAULT '' COMMENT '商品货号',
`goods_name` varchar(120) NOT NULL DEFAULT '' COMMENT '商品名',
`click_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '浏览量',
`brand_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '品牌id',
`suppliers_id` varchar(100) NOT NULL DEFAULT '' COMMENT '供应商id',
`market_price` decimal(9,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '市场价格',
`shop_price` decimal(9,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '本店售价',
`promote_price` decimal(9,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '促销价格',
`promote_start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '促销开始日期',
`promote_end_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '促销结束日期',
`keywords` varchar(255) NOT NULL DEFAULT '' COMMENT '商品关键字',
`goods_brief` varchar(255) NOT NULL DEFAULT '' COMMENT '商品简介',
`goods_desc` text NOT NULL COMMENT '商品详情',
`goods_img` varchar(255) NOT NULL DEFAULT '' COMMENT '商品图' ,
`is_putaway` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否上架',
`putaway_time` timestamp NOT NULL CURRENT_TIMESTAMP COMMENT '上架时间',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`sort_order` smallint(4) unsigned NOT NULL DEFAULT '100' COMMENT '排序',
`is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否被删除',
`is_best` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '精品',
`is_new` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '新品',
`is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '热销',
`is_promote` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '促销',
`goods_for` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '商品归属于哪个活动',
`give_integral` int(11) NOT NULL DEFAULT '-1' COMMENT '赠送积分',
`rank_integral` int(11) NOT NULL DEFAULT '-1' COMMENT '赠送等级经验',
'rest_count' mediumint(5) NOT NULL DEFAULT '0' COMMENT '库存',
'sell_count' mediumint(5) NOT NULL DEFAULT '0' COMMENT '销量',
PRIMARY KEY(`goods_id`),
KEY`goods_sn`(`goods_sn`),
KEY`cat_id`(`cat_id`),
KEY`brand_id`(`brand_id`),
KEY`promote_end_date`(`promote_end_date`),
KEY`promote_start_date`(`promote_start_date`),
KEY`goods_number`(`goods_number`),
KEY`sort_order`(`sort_order`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8
2.商品扩展分类关系表（跟商品表为多对多的关系）
CREATE TABLE `wl_goods_cat`(
`goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY(`goods_id`,`cat_id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8
3.属性表
CREATE TABLE `wl_attribute` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '商品类型id',
`attr_name` varchar(60) NOT NULL DEFAULT '' COMMENT '属性项名',
`attr_input_type` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '属性类型，唯一性，单选，复选',
`attr_values` text NOT NULL COMMENT '属性值，用“，”间隔',
`attr_index` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否可以进行筛选',
PRIMARY KEY (`id`),
KEY `type_id` (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
4.商品属性值表
CREATE TABLE `wl_goods_attr`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
`attr_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '属性项id',
`attr_value` text NOT NULL COMMENT '保存单个属性值',
`attr_price` smallint(5) NOT NULL DEFAULT '0' COMMENT '规格价格变动',
PRIMARY KEY(`id`),
KEY`goods_id`(`goods_id`),
KEY`attr_id`(`attr_id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8
5.商品类型表
CREATE TABLE `wl_goods_type`(
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`cat_name` varchar(60) NOT NULL DEFAULT'',
`enabled` tinyint(1) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY(`id`)
)ENGINE=MyISAMAUTO_INCREMENT=11DEFAULT CHARSET=utf8
6.商品分类表，添加分类时，可列出商品类型下面的可被索引到的属性项放至筛选属性中
CREATE TABLE `wl_category`(
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`cat_name` varchar(90) NOT NULL DEFAULT'',
`keywords` varchar(255) NOT NULL DEFAULT'',
`cat_desc` varchar(255) NOT NULL DEFAULT'',
`parent_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`sort_order` tinyint(1) unsigned NOT NULL DEFAULT '50',
`show_in_nav` tinyint(1) NOT NULL DEFAULT '0',
`is_show` tinyint(1) unsigned NOT NULL DEFAULT '1',
`filter_attr` varchar(255) NOT NULL DEFAULT '0' COMMENT '属性项id，以逗号隔开',
PRIMARY KEY(`id`),
KEY`parent_id`(`parent_id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8
