Java - 自己动手之在线书店(2)
数据库设计,我这里用的PowerDesigner 15(破解版),需要下载的朋友可以去电驴搜索一下.
我这里只是简单设计了一下,也没有什么深入考虑,毕竟整个项目就比较简单 也不是什么正规的.
上图一个:
PD设计好了以后,Ctrl+G生成脚本文件:
drop table if exists tbBookAuthor;
drop table if exists tbBookCategory;
drop table if exists tbBookInfo;
drop table if exists tbBookPublisher;
drop table if exists tbManagerInfo;
drop table if exists tbOrderInfo;
drop table if exists tbOrderItem;
drop table if exists tbStoreInfo;
drop table if exists tbUserAddress;
drop table if exists tbUserInfo;
drop table if exists tbrBookAuthor;
/*==============================================================*/
/* Table: tbBookAuthor */
/*==============================================================*/
create table tbBookAuthor
(
author_id int not null auto_increment,
author_name varchar(64) not null,
author_info text,
author_memo text,
primary key (author_id)
);
alter table tbBookAuthor comment '图书作者信息表';
/*==============================================================*/
/* Table: tbBookCategory */
/*==============================================================*/
create table tbBookCategory
(
category_id int not null auto_increment,
category_name varchar(32) not null,
category_description text,
category_memo text,
primary key (category_id)
);
alter table tbBookCategory comment '图书分类表';
/*==============================================================*/
/* Table: tbBookInfo */
/*==============================================================*/
create table tbBookInfo
(
book_id int not null auto_increment,
category_id int,
publisher_id int,
book_name varchar(64) not null,
book_price float,
book_description text,
book_memo text,
book_nowprice float,
book_storeno int,
primary key (book_id)
);
alter table tbBookInfo comment '图书信息表';
/*==============================================================*/
/* Table: tbBookPublisher */
/*==============================================================*/
create table tbBookPublisher
(
publisher_id int not null auto_increment,
publisher_name varchar(32) not null,
publisher_addr varchar(64),
publisher_phone varchar(18),
publisher_email varchar(32),
publisher_url varchar(64),
publisher_description text,
publisher_memo text,
primary key (publisher_id)
);
alter table tbBookPublisher comment '图书出版商信息表';
/*==============================================================*/
/* Table: tbManagerInfo */
/*==============================================================*/
create table tbManagerInfo
(
manager_id int not null auto_increment,
manager_name varchar(32) not null,
manager_password varchar(32) not null,
primary key (manager_id)
);
alter table tbManagerInfo comment '书店管理员信息表';
/*==============================================================*/
/* Table: tbOrderInfo */
/*==============================================================*/
create table tbOrderInfo
(
order_id int not null auto_increment,
addr_id int,
order_tel varchar(18) not null,
order_time datetime,
order_memo text,
primary key (order_id)
);
alter table tbOrderInfo comment '订单信息表';
/*==============================================================*/
/* Table: tbOrderItem */
/*==============================================================*/
create table tbOrderItem
(
item_id int not null auto_increment,
order_id int,
book_id int,
item_number int,
item_memo text,
primary key (item_id)
);
alter table tbOrderItem comment '订单中每一项信息表';
/*==============================================================*/
/* Table: tbStoreInfo */
/*==============================================================*/
create table tbStoreInfo
(
store_name varchar(32) not null,
store_addr varchar(64),
store_phone varchar(18),
store_email varchar(64),
store_descriptrion text,
store_memo text,
primary key (store_name)
);
alter table tbStoreInfo comment '书店基础信息表,包含书店的名字,地址,电话等信息';
/*==============================================================*/
/* Table: tbUserAddress */
/*==============================================================*/
create table tbUserAddress
(
addr_id int not null auto_increment,
user_id int,
addr_addr varchar(64) not null,
addr_memo text,
primary key (addr_id)
);
alter table tbUserAddress comment '用户的收货地址';
/*==============================================================*/
/* Table: tbUserInfo */
/*==============================================================*/
create table tbUserInfo
(
user_id int not null auto_increment,
user_name varchar(32) not null,
user_password varchar(32) not null,
user_email varchar(64),
user_phone varchar(18),
user_description text,
user_memo text,
primary key (user_id)
);
alter table tbUserInfo comment '用户表';
/*==============================================================*/
/* Table: tbrBookAuthor */
/*==============================================================*/
create table tbrBookAuthor
(
rba_ID int not null auto_increment,
book_id int,
author_id int,
primary key (rba_ID)
);
alter table tbrBookAuthor comment '图书和作者关系表,多对多';
alter table tbBookInfo add constraint FK_BookCategory foreign key (category_id)
references tbBookCategory (category_id) on delete set null on update cascade;
alter table tbBookInfo add constraint FK_BookPublisher foreign key (publisher_id)
references tbBookPublisher (publisher_id) on delete set null on update cascade;
alter table tbOrderInfo add constraint FK_OrderAddress foreign key (addr_id)
references tbUserAddress (addr_id) on update cascade;
alter table tbOrderItem add constraint FK_BookOrder foreign key (book_id)
references tbBookInfo (book_id) on update cascade;
alter table tbOrderItem add constraint FK_OrderItem foreign key (order_id)
references tbOrderInfo (order_id) on update cascade;
alter table tbUserAddress add constraint FK_UserAddress foreign key (user_id)
references tbUserInfo (user_id) on delete cascade on update cascade;
alter table tbrBookAuthor add constraint FK_rAuthor foreign key (author_id)
references tbBookAuthor (author_id) on delete cascade on update cascade;
alter table tbrBookAuthor add constraint FK_rBook foreign key (book_id)
references tbBookInfo (book_id) on delete restrict on update cascade;
整个文件的下载地址
