外卖项目进展②后端环境搭建及数据库初步设计
1.SpringBoot环境搭建
2.数据库的设计
①概念模型
②物理模型
③生成的takeout.sql文件
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2020/12/3 12:03:13 */
/*==============================================================*/
drop table if exists address;
drop table if exists coupon;
drop table if exists fullreduction;
drop table if exists getmoney;
drop table if exists gooddetails;
drop table if exists goods_order;
drop table if exists goodsassess;
drop table if exists goodstype;
drop table if exists manager;
drop table if exists order_details;
drop table if exists orders_conpons;
drop table if exists relationship_manager_rider;
drop table if exists relationship_manager_store;
drop table if exists relationship_manager_user;
drop table if exists relationship_store_coupon;
drop table if exists relationship_store_fullreduction;
drop table if exists relationship_store_goodstype;
drop table if exists rider;
drop table if exists store;
drop table if exists user;
drop table if exists usercoupon;
/*==============================================================*/
/* Table: address */
/*==============================================================*/
create table address
(
address_id int not null,
user_id int not null,
province varchar(20),
city varchar(20),
area varchar(20),
address varchar(50),
user_name varchar(20),
phonenumber varchar(20),
primary key (address_id, user_id)
);
/*==============================================================*/
/* Table: coupon */
/*==============================================================*/
create table coupon
(
coupon_id int not null,
coupon_money int,
coupon_require_number int,
coupon_begin_time datetime,
coupon_end_yime datetime,
primary key (coupon_id)
);
/*==============================================================*/
/* Table: fullreduction */
/*==============================================================*/
create table fullreduction
(
fullreduction_id int not null,
fullreduction_money int,
reduction_money int,
with_coupon bool,
primary key (fullreduction_id)
);
/*==============================================================*/
/* Table: getmoney */
/*==============================================================*/
create table getmoney
(
rider_id int not null,
order_id int not null,
gettime datetime,
user_access varchar(20),
income int,
primary key (rider_id, order_id)
);
/*==============================================================*/
/* Table: gooddetails */
/*==============================================================*/
create table gooddetails
(
goods_id int not null,
type_id int not null,
goods_name varchar(20),
goods_money numeric(10,2),
coupon_money numeric(10,2),
primary key (goods_id, type_id)
);
/*==============================================================*/
/* Table: goods_order */
/*==============================================================*/
create table goods_order
(
order_id int not null,
store_id int not null,
user_id varchar(20),
rider_id varchar(20),
original_money numeric(10,1),
end_money numeric(10,1),
fullreduction_id varchar(20),
coupon_id varchar(20),
order_time datetime,
require_arrive_time datetime,
address_id varchar(50),
order_status varchar(20),
primary key (order_id, store_id)
);
/*==============================================================*/
/* Table: goodsassess */
/*==============================================================*/
create table goodsassess
(
goods_id int not null,
store_id int not null,
user_id int not null,
content varchar(200),
time datetime not null,
level int,
photo longblob,
primary key (goods_id, store_id, user_id, time)
);
/*==============================================================*/
/* Table: goodstype */
/*==============================================================*/
create table goodstype
(
type_id int not null,
type_name varchar(20),
goods_count int,
primary key (type_id)
);
/*==============================================================*/
/* Table: manager */
/*==============================================================*/
create table manager
(
manager_id int not null,
manager_name varchar(20),
password varchar(20),
primary key (manager_id)
);
/*==============================================================*/
/* Table: order_details */
/*==============================================================*/
create table order_details
(
order_id int not null,
goods_id int not null,
count int,
money numeric(10,1),
per_reduce_money int,
primary key (order_id, goods_id)
);
/*==============================================================*/
/* Table: orders_conpons */
/*==============================================================*/
create table orders_conpons
(
user_id int not null,
store_id int not null,
conpon_id int not null,
conpon_require_number int,
ordercount int,
primary key (user_id, store_id, conpon_id)
);
/*==============================================================*/
/* Table: relationship_manager_rider */
/*==============================================================*/
create table relationship_manager_rider
(
manager_id int not null,
rider_id int not null,
primary key (manager_id, rider_id)
);
/*==============================================================*/
/* Table: relationship_manager_store */
/*==============================================================*/
create table relationship_manager_store
(
manager_id int not null,
store_id int not null,
primary key (manager_id, store_id)
);
/*==============================================================*/
/* Table: relationship_manager_user */
/*==============================================================*/
create table relationship_manager_user
(
manager_id int not null,
user_id int not null,
primary key (manager_id, user_id)
);
/*==============================================================*/
/* Table: relationship_store_coupon */
/*==============================================================*/
create table relationship_store_coupon
(
coupon_id int not null,
store_id int not null,
primary key (coupon_id, store_id)
);
/*==============================================================*/
/* Table: relationship_store_fullreduction */
/*==============================================================*/
create table relationship_store_fullreduction
(
store_id int not null,
fullreduction_id int not null,
primary key (store_id, fullreduction_id)
);
/*==============================================================*/
/* Table: relationship_store_goodstype */
/*==============================================================*/
create table relationship_store_goodstype
(
store_id int not null,
type_id int not null,
primary key (store_id, type_id)
);
/*==============================================================*/
/* Table: rider */
/*==============================================================*/
create table rider
(
rider_id int not null,
rider_name varchar(20),
rider_entrydate datetime,
rider_identity varchar(20),
primary key (rider_id)
);
/*==============================================================*/
/* Table: store */
/*==============================================================*/
create table store
(
store_id int not null,
store_name varchar(20),
store_level numeric(1,1),
store_per_consumption int,
store_totalsales int,
primary key (store_id)
);
/*==============================================================*/
/* Table: user */
/*==============================================================*/
create table user
(
user_id int not null,
user_name varchar(20),
sex varchar(4),
password varchar(20),
phonenumber varchar(20),
email varchar(20),
city varchar(20),
registration_time datetime,
member bool,
member_end_time datetime,
primary key (user_id)
);
/*==============================================================*/
/* Table: usercoupon */
/*==============================================================*/
create table usercoupon
(
user_id int not null,
coupon_id int not null,
store_id int not null,
coupon_money int,
coupon_count int,
coupon_end_time datetime,
primary key (user_id, coupon_id, store_id)
);
alter table address add constraint FK_Reference_20 foreign key (user_id)
references user (user_id) on delete restrict on update restrict;
alter table getmoney add constraint FK_Reference_16 foreign key (rider_id)
references rider (rider_id) on delete restrict on update restrict;
alter table gooddetails add constraint FK_Reference_13 foreign key (type_id)
references goodstype (type_id) on delete restrict on update restrict;
alter table goods_order add constraint FK_Reference_23 foreign key (store_id)
references store (store_id) on delete restrict on update restrict;
alter table goodsassess add constraint FK_Reference_14 foreign key (store_id)
references store (store_id) on delete restrict on update restrict;
alter table goodsassess add constraint FK_Reference_15 foreign key (user_id)
references user (user_id) on delete restrict on update restrict;
alter table orders_conpons add constraint FK_Reference_21 foreign key (user_id)
references user (user_id) on delete restrict on update restrict;
alter table orders_conpons add constraint FK_Reference_22 foreign key (store_id)
references store (store_id) on delete restrict on update restrict;
alter table relationship_manager_rider add constraint FK_relationship_manager_rider foreign key (manager_id)
references manager (manager_id) on delete restrict on update restrict;
alter table relationship_manager_rider add constraint FK_relationship_manager_rider2 foreign key (rider_id)
references rider (rider_id) on delete restrict on update restrict;
alter table relationship_manager_store add constraint FK_relationship_manager_store foreign key (manager_id)
references manager (manager_id) on delete restrict on update restrict;
alter table relationship_manager_store add constraint FK_relationship_manager_store2 foreign key (store_id)
references store (store_id) on delete restrict on update restrict;
alter table relationship_manager_user add constraint FK_relationship_manager_user foreign key (manager_id)
references manager (manager_id) on delete restrict on update restrict;
alter table relationship_manager_user add constraint FK_relationship_manager_user2 foreign key (user_id)
references user (user_id) on delete restrict on update restrict;
alter table relationship_store_coupon add constraint FK_relationship_store_coupon foreign key (coupon_id)
references coupon (coupon_id) on delete restrict on update restrict;
alter table relationship_store_coupon add constraint FK_relationship_store_coupon2 foreign key (store_id)
references store (store_id) on delete restrict on update restrict;
alter table relationship_store_fullreduction add constraint FK_relationship_store_fullreduction foreign key (store_id)
references store (store_id) on delete restrict on update restrict;
alter table relationship_store_fullreduction add constraint FK_relationship_store_fullreduction2 foreign key (fullreduction_id)
references fullreduction (fullreduction_id) on delete restrict on update restrict;
alter table relationship_store_goodstype add constraint FK_relationship_store_goodstype foreign key (store_id)
references store (store_id) on delete restrict on update restrict;
alter table relationship_store_goodstype add constraint FK_relationship_store_goodstype2 foreign key (type_id)
references goodstype (type_id) on delete restrict on update restrict;
alter table usercoupon add constraint FK_Reference_17 foreign key (user_id)
references user (user_id) on delete restrict on update restrict;
alter table usercoupon add constraint FK_Reference_18 foreign key (coupon_id)
references coupon (coupon_id) on delete restrict on update restrict;
alter table usercoupon add constraint FK_Reference_19 foreign key (store_id)
references store (store_id) on delete restrict on update restrict;
④表的生成
浙公网安备 33010602011771号