点餐系统一指尖香飞数据库数据表设计1-广告表-商品表-商店表-订

# Host: 127.0.0.1  (Version 8.0.12)
# Date: 2019-01-27 21:51:29
# Generator: MySQL-Front 6.1  (Build 1.21)


#
# Structure for table "ad"
#

DROP TABLE IF EXISTS `ad`;
CREATE TABLE `ad` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'id',
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '标题',
  `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '图片',
  `type` tinyint(1) unsigned DEFAULT '0' COMMENT '备用字段广告类型：1=站内广告，2=站外广告',
  `src` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '地址源',
  `state` tinyint(1) unsigned DEFAULT '1' COMMENT '状态1正常',
  `createTime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `deleteTime` int(10) unsigned DEFAULT NULL COMMENT '删除时间',
  `user_id` bigint(20) unsigned DEFAULT NULL COMMENT '用户id/或商户id根据用户类型判断',
  `userType` tinyint(1) unsigned DEFAULT '1' COMMENT '用户类型1商户',
  `residues` int(11) unsigned DEFAULT '0' COMMENT '剩余点击次数',
  `areaCode` int(6) unsigned zerofill DEFAULT '000000' COMMENT '投放地区代码',
  `longitude` float(10,6) unsigned zerofill DEFAULT '000.000000' COMMENT '地址坐标经度',
  `latitude` float(10,6) unsigned zerofill DEFAULT '000.000000' COMMENT '地址坐标纬度',
  `range` int(3) unsigned DEFAULT '0' COMMENT '投放范围（公里数）：0=不限；',
  `area` varchar(255) DEFAULT NULL COMMENT '地区：北京-城区-东城区',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='广告表';

#
# Data for table "ad"
#


#
# Structure for table "goods"
#

DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `id` char(32) NOT NULL DEFAULT '' COMMENT 'id',
  `title` varchar(255) DEFAULT '无标题' COMMENT '标题',
  `shelve` tinyint(1) DEFAULT '1' COMMENT '上架状态；1=正常，0=下架',
  `state` int(10) DEFAULT '1' COMMENT '1=正常，时间戳=已经删除。软删除是否为删除状态',
  `tip` varchar(255) DEFAULT '自营' COMMENT '认证标签',
  `createTime` int(10) unsigned DEFAULT '1500000000' COMMENT '创建时间',
  `updateTime` int(10) unsigned zerofill DEFAULT '1500000000' COMMENT '更新时间',
  `stock` int(11) unsigned DEFAULT '1' COMMENT '库存',
  `price` float(8,2) unsigned DEFAULT '0.00' COMMENT '默认价格',
  `shop_id` char(32) DEFAULT NULL COMMENT '店铺id',
  `image` varchar(255) DEFAULT NULL COMMENT '主图',
  `oldPrice` float(8,2) unsigned DEFAULT NULL COMMENT '现价',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品主表';

#
# Data for table "goods"
#


#
# Structure for table "goods_image"
#

DROP TABLE IF EXISTS `goods_image`;
CREATE TABLE `goods_image` (
  `id` char(32) NOT NULL DEFAULT '',
  `goods_id` char(32) DEFAULT NULL COMMENT '关联商品id',
  `src` varchar(255) DEFAULT NULL COMMENT '图片地址',
  `isdelete` int(11) unsigned DEFAULT NULL COMMENT '是否已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品图片列表';

#
# Data for table "goods_image"
#


#
# Structure for table "goods_version"
#

DROP TABLE IF EXISTS `goods_version`;
CREATE TABLE `goods_version` (
  `id` char(32) NOT NULL DEFAULT '',
  `name` varchar(255) DEFAULT '默认' COMMENT '版本名称',
  `price` float(8,2) unsigned DEFAULT '123456.70' COMMENT '现价格',
  `oldPrice` float(8,2) unsigned DEFAULT '0.00' COMMENT '原价',
  `image` varchar(255) DEFAULT 'www' COMMENT '图片',
  `stock` int(5) unsigned DEFAULT '1' COMMENT '库存',
  `goods_id` char(32) DEFAULT NULL COMMENT '关联商品id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品版本';


#
# Structure for table "order"
#

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
  `shop_id` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '店铺id',
  `shopName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '店铺名',
  `sum` float(8,2) unsigned DEFAULT '0.00' COMMENT '订单总额',
  `pay` float(8,2) unsigned DEFAULT '0.00' COMMENT '实付金额',
  `abstract` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '订单摘要',
  `user_id` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '买家用户id',
  `createTime` int(10) unsigned zerofill DEFAULT '0000000000' COMMENT '下单时间',
  `updateTime` int(10) unsigned zerofill DEFAULT '0000000000' COMMENT '更新时间',
  `isDelete` int(10) unsigned zerofill DEFAULT '0000000000' COMMENT '删除时间',
  `note` varchar(255) DEFAULT NULL COMMENT '买家备注',
  `state` int(1) unsigned zerofill DEFAULT '0' COMMENT '订单状态',
  `userDelete` tinyint(1) unsigned DEFAULT '0' COMMENT '用户是否已删除',
  `shopDelete` tinyint(1) unsigned DEFAULT '0' COMMENT '店铺是否已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单主表';


#
# Structure for table "order_goods"
#

DROP TABLE IF EXISTS `order_goods`;
CREATE TABLE `order_goods` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商品名',
  `versionName` varchar(255) DEFAULT NULL COMMENT '版本名',
  `price` float(8,2) unsigned DEFAULT '0.00' COMMENT '价格',
  `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商品图片',
  `number` int(11) unsigned zerofill DEFAULT NULL COMMENT '数量',
  `note` varchar(100) DEFAULT NULL COMMENT '买家备注',
  `order_id` bigint(20) unsigned DEFAULT NULL COMMENT '订单id',
  `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT '商品id',
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `order_goods_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单分表—商品列表';


#
# Structure for table "shop"
#

DROP TABLE IF EXISTS `shop`;
CREATE TABLE `shop` (
  `id` char(32) NOT NULL DEFAULT '',
  `user_id` int(11) unsigned DEFAULT NULL COMMENT '用户id',
  `name` varchar(255) DEFAULT NULL COMMENT '店铺名',
  `state` int(11) unsigned DEFAULT NULL COMMENT '店铺状态，1=正常，0=待审核',
  `abstract` varchar(255) DEFAULT NULL COMMENT '摘要，要点',
  `logo` varchar(255) DEFAULT NULL COMMENT '店标',
  `longitude` float(8,5) unsigned DEFAULT '34.00000' COMMENT '精度',
  `latitude` float(8,5) unsigned DEFAULT '1.00000' COMMENT '纬度',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `areaCode` int(6) unsigned DEFAULT NULL COMMENT '城市代码',
  `tradingAreaCode` int(6) unsigned zerofill DEFAULT '100000' COMMENT '商圈代码',
  `detail` text COMMENT '详情',
  `notice` char(100) DEFAULT '' COMMENT '通知',
  `area` varchar(20) DEFAULT NULL COMMENT '地区 北京-城区-东城区',
  `createTime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `updateTime` int(10) unsigned DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='店铺';