Area 區(qū)域
- areaId
- name 名稱
- priority 權(quán)重
- createTime 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
- enable 是否啟用
- parentId 父節(jié)點(diǎn)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 狀態(tài) 0是啟用1是禁用
- userType 用戶類型 1:超管2:店主3:普通用戶4:微信用戶5:手機(jī)號(hào)用戶
- createTime 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
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:無(wú)效用戶1:超管2:店主3:普通用戶4:微信用戶5:手機(jī)號(hào)用戶',
`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 創(chuàng)建時(shí)間
- personInfo 用戶信息 user_id關(guān)聯(lián)
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 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
- personInfo 用戶信息 user_id關(guān)聯(lián)
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 權(quán)重
- title 標(biāo)題
- clickUrl 點(diǎn)擊鏈接
- imgUrl 圖片鏈接
- status 是否可用 0可用 1不可用
- createTime 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
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 權(quán)重
- createTime 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
- parent 無(wú)限樹 關(guān)聯(lián)本身的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 權(quán)重
- imgUrl 圖片地址
- name 名稱
- desc 描述
- address 地址
- phone 聯(lián)系電話
- area 區(qū)域
- category 店鋪類別
- owner 店鋪所有人
- enable 是否啟用 0啟用 1禁用 2審核
- notice 通知
- createTime 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
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 權(quán)重
- createTime 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
- shopId 關(guān)聯(lián)店鋪的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 權(quán)重
- title 標(biāo)題
- imgUrl 圖片鏈接
- createTime 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
- 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 權(quán)重
- imgUrl 圖片地址
- name 名稱
- desc 描述
- normalPrice 價(jià)格
- promotionPrice 折扣價(jià)
- shop 店鋪
- category 商品類別
- banner 商品banner list
- status 是否啟用 0啟用 1下架 2審核
- createTime 創(chuàng)建時(shí)間
- lastEditTime 修改時(shí)間
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)
