Area 区域
- areaId
- name 名称
- priority 权重
- createTime 创建时间
- lastEditTime 修改时间
- enable 是否启用
- parentId 父节点id
use o2o;
create table `tb_area`(
`area_id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`priority` int(2) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
`parent_id` int(5) NOT NULL DEFAULT '0',
primary key(`area_id`),
unique key `UK_AREA`(`name`),
constraint `FK_AREA_SELF` foreign key(`parent_id`) references `tb_area`(`area_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
personInfo 用户信息
- personId
- name 昵称
- imgUrl 图像地址
- email 邮箱
- gender 性别
- status 状态 0是启用1是禁用
- userType 用户类型 1:超管2:店主3:普通用户4:微信用户5:手机号用户
- createTime 创建时间
- lastEditTime 修改时间
use o2o;
create table `tb_person_info`(
`person_id` BIGINT NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`img_url`varchar(1024) DEFAULT NULL,
`email`varchar(1024) DEFAULT NULL,
`gender` int(1) DEFAULT NULL,
`status` int(2) NOT NULL DEFAULT '0' COMMENT '0是启用1是禁用',
`user_type` int(1) NOT NULL DEFAULT '0' COMMENT '0:无效用户1:超管2:店主3:普通用户4:微信用户5:手机号用户',
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
primary key(`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
wechatAuth 微信用户
- wechatAuthId
- openId
- createTime 创建时间
- personInfo 用户信息 user_id关联
use o2o;
create table `tb_wechat_auth`(
`wechat_auth_id` BIGINT NOT NULL AUTO_INCREMENT,
`open_id` varchar(1024) NOT NULL,
`user_id` BIGINT NOT NULL,
`create_time` DATETIME DEFAULT NULL,
primary key (`wechat_auth_id`),
unique key `UK_WECHAT_OPEN_ID`(`open_id`),
constraint `FK_WECHAT_AUTH_PERSION` foreign key(`user_id`) references `tb_person_info`(`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
registerAuth 微信用户
- registerAuthId
- userName 用户名
- password 密码
- createTime 创建时间
- lastEditTime 修改时间
- personInfo 用户信息 user_id关联
use o2o;
create table `tb_register_auth`(
`register_auth_id` BIGINT NOT NULL AUTO_INCREMENT,
`user_id` BIGINT NOT NULL,
`user_name` varchar(128) NOT NULL,
`password` varchar(128) NOT NULL,
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
primary key(`register_auth_id`),
unique key `UK_REGISTER_AUTH`(`user_name`),
constraint `FK_REGISTER_AUTH_PERSON` foreign key(`user_id`) references `tb_person_info`(`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
banner
- bannerId
- priority 权重
- title 标题
- clickUrl 点击链接
- imgUrl 图片链接
- status 是否可用 0可用 1不可用
- createTime 创建时间
- lastEditTime 修改时间
use o2o;
create table `tb_banner`(
`banner_id` int(10) NOT NULL AUTO_INCREMENT,
`title` varchar(128) NOT NULL,
`click_url` varchar(2048) DEFAULT NULL,
`img_url` varchar(2048) NOT NULL,
`status` int(2) NOT NULL DEFAULT '0' COMMENT '0是启用1是禁用',
`priority` int(2) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
primary key(`banner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
shopCategory店铺类别
- shopCategoryId
- name 名称
- desc 注释
- imgUrl 图片链接
- priority 权重
- createTime 创建时间
- lastEditTime 修改时间
- parent 无限树 关联本身的shopCategoryId
use o2o;
create table `tb_shop_category`(
`shop_category_id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
`desc` varchar(2048) DEFAULT NULL,
`img_url` varchar(2048) DEFAULT NULL,
`priority` int(2) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
`parent_id` int(10) DEFAULT NULL,
primary key(`shop_category_id`),
constraint `FK_SHOP_CATEGORY_SELF` foreign key(`parent_id`) references `tb_shop_category`(`shop_category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
shop店铺
- shopId
- priority 权重
- imgUrl 图片地址
- name 名称
- desc 描述
- address 地址
- phone 联系电话
- area 区域
- category 店铺类别
- owner 店铺所有人
- enable 是否启用 0启用 1禁用 2审核
- notice 通知
- createTime 创建时间
- lastEditTime 修改时间
use o2o;
create table `tb_shop`(
`shop_id` BIGINT NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
`phone` varchar(128) NOT NULL,
`desc` varchar(2048) DEFAULT NULL,
`address` varchar(2048) DEFAULT NULL,
`img_url` varchar(2048) DEFAULT NULL,
`notice` varchar(2048) DEFAULT NULL,
`priority` int(2) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
`enable` int(1) NOT NULL DEFAULT '2' COMMENT '0启用 1禁用 2审核',
`owner_id` BIGINT NOT NULL,
`area_id` int(5) NOT NULL,
`category_id` int(10) NOT NULL,
primary key(`shop_id`),
unique key `UK_SHOP_NAME`(`name`),
constraint `FK_SHOP_OWNER` foreign key(`owner_id`) references `tb_person_info`(`person_id`),
constraint `FK_SHOP_AREA` foreign key(`area_id`) references `tb_area`(`area_id`),
constraint `FK_SHOP_CATEGORY` foreign key(`category_id`) references `tb_shop_category`(`shop_category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
商品类别
- productCategoryId
- name 名称
- imgUrl 图片链接
- priority 权重
- createTime 创建时间
- lastEditTime 修改时间
- shopId 关联店铺的Id
use o2o;
create table `tb_product_category`(
`product_category_id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
`img_url` varchar(2048) DEFAULT NULL,
`priority` int(2) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
`shop_id` BIGINT DEFAULT NULL,
primary key(`product_category_id`),
constraint `FK_PRODUCT_CATEGORY_SHOP` foreign key(`shop_id`) references `tb_shop`(`shop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
商品banner
- productBannerId
- priority 权重
- title 标题
- imgUrl 图片链接
- createTime 创建时间
- lastEditTime 修改时间
- product_id 商品ID
use o2o;
create table `tb_product_banner`(
`product_banner_id` int(10) NOT NULL AUTO_INCREMENT,
`title` varchar(128) NOT NULL,
`img_url` varchar(2048) NOT NULL,
`priority` int(2) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
`product_id` BIGINT NOT NULL,
primary key(`product_banner_id`),
constraint `FK_PRODUCT_BANNER_PRODUCT` foreign key(`product_id`) references `tb_product`(`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
product商品
- productId
- priority 权重
- imgUrl 图片地址
- name 名称
- desc 描述
- normalPrice 价格
- promotionPrice 折扣价
- shop 店铺
- category 商品类别
- banner 商品banner list
- status 是否启用 0启用 1下架 2审核
- createTime 创建时间
- lastEditTime 修改时间
use o2o;
create table `tb_product`(
`product_id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
`desc` varchar(2048) DEFAULT NULL,
`img_url` varchar(2048) DEFAULT NULL,
`priority` int(2) NOT NULL DEFAULT `0`,
`normal_price` int(10) NOT NULL DEFAULT `0`,
`promotion_price` int(10) NOT NULL DEFAULT `0`,
`create_time` datetime DEFAULT NULL,
`last_edit_time` datetime DEFAULT NULL,
`status` int(1) NOT NULL DEFAULT `2` COMMENT `0启用 1禁用 2审核`,
`shop_id` int(10) NOT NULL,
`banner_id` int(10) DEFAULT NULL,
`category_id` int(10) NOT NULL,
primary key(`product_id`),
unique key `UK_shop_name`(`name`),
constraint `fk_product_shop` foreign key(`shop_id`) references `tb_shop`(`shop_id`),
constraint `fk_product_banner` foreign key(`banner_id`) references `tb_product_banner`(`banner_id`),
constraint `fk_product_category` foreign key(`product_category_id`) references `tb_product_category`(`product_category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SQL
alter table tb_wechat_auth add unique index(open_id)
alter table o2o rename tb_area
alter table tb_area add column name varchar(10)
alter table tb_area drop column name
alter table tb_area modify address char(10)
alter table tb_area change address address char(40)
alter table tb_area change column address address1 varchar(30)
