商城系统商品相关数据库设计

DROP TABLE IF EXISTS `T_CATAGORY`;
CREATE TABLE `T_CATAGORY` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '目录id',
  `NAME` varchar(200) NOT NULL DEFAULT '' COMMENT '目录名称',
  `IMG_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '图片id',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `T_GOODS`;
CREATE TABLE `T_GOODS` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `NAME` varchar(200) NOT NULL DEFAULT '' COMMENT '商品名称',
`CREATE_TIME` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`UPDATE_TIME` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
  `CATA_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '目录id',
  `IMG_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '图片id',
 `GOODS_DESC` longtext  NOT NULL DEFAULT '' COMMENT '商品说明',
`PULISH_STATE` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '发布状态(0未上架1已上架)',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `T_SPEC`;
CREATE TABLE `T_SPEC` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '规格id',
  `NAME` varchar(200) NOT NULL DEFAULT '' COMMENT '规格名称',
  `GOODS_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `TYPE` varchar(40)  NOT NULL DEFAULT '' COMMENT '类型(规格|配件)',
`MAX_NUM` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '最大可选值(0为无限制)',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `T_SPEC_VALUE`;
CREATE TABLE `T_SPEC` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '规格值id',
  `SPEC_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '规格id',
  `GOODS_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `SPEC_VAL` varchar(200)  NOT NULL DEFAULT '' COMMENT '规格值',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `T_SKU`;
CREATE TABLE `T_SKU` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '规格id',
  `GOODS_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `DR_CODE` mediumblob  COMMENT '二维码',
`B_CODE`  mediumblob  COMMENT '条形码',
`PRICE` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '价格',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `T_SKU_VALUE`;
CREATE TABLE `T_SKU_VALUE` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '规格值id',
  `SKU_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '规格组id',
  `GOODS_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `SKU_VAL` varchar(200)  NOT NULL DEFAULT '' COMMENT '规格值',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `T_ORDER`;
CREATE TABLE `T_ORDER` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `USER_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `CREATE_TIME` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`FINISH_TIME` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '完成时间',
`PRICE` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '价格',
`ORDER_STATE` varchar(200)  NOT NULL DEFAULT '' COMMENT '订单状态',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;




DROP TABLE IF EXISTS `T_ORDER_GOODS`;
CREATE TABLE `T_ORDER_GOODS` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单商品id',
  `NAME` varchar(200) NOT NULL DEFAULT '' COMMENT '订单商品名称',
  `GOODS_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `IMG_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '图片id',
 `PRICE` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商品价格',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `T_ORDER_GOODS_VALUE`;
CREATE TABLE `T_ORDER_GOODS_VALUE` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '规格值id',
  `ORDER_GOODS_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '订单商品id',
 `SKU_NAME` varchar(200)  NOT NULL DEFAULT '' COMMENT '规格名称',
  `SKU_VAL` varchar(200)  NOT NULL DEFAULT '' COMMENT '规格值',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `T_FILE`;
CREATE TABLE `T_FILE` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '文件id',
  `RELATION_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '关联id',
 `FILE_NAME` varchar(200)  NOT NULL DEFAULT '' COMMENT '文件名称',
  `FILE_TYPE` varchar(40)  NOT NULL DEFAULT '' COMMENT '类型',
  `FILE_EX` varchar(40)  NOT NULL DEFAULT '' COMMENT '文件扩展名',
  `FILE_SIZE` int(11) unsigned  NOT NULL DEFAULT '0' COMMENT '文件大小',
  `SORT_ID` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8