外卖项目进展②后端环境搭建及数据库初步设计

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;

 

④表的生成

外卖项目进展②后端环境搭建及数据库初步设计

上一篇:安装SSH2 for PHP以在OSX 10.9.1的Xampp Apache服务器上运行


下一篇:IntelliJ IDEA 自带的 HTTP Client 接口调用插件,吊打 Postman