数据库物理模型:
sql代码:
create table consignee_management
(
consignee_id int not null auto_increment,
user_id int,
consignee_name varchar(50),
consignee_address varchar(1000),
consignee_code varchar(10),
consignee_phone varchar(11),
primary key (consignee_id)
);
create table goods_info
(
goods_id int not null auto_increment,
goods_name varchar(200),
goods_price double,
goods_url varchar(1000),
goods_desc varchar(2000),
goods_state varchar(10) comment '1 上架 2 下架',
primary key (goods_id)
);
create table order_detail
(
order_detail_id int not null auto_increment,
order_id int,
goods_id int,
orde_number int,
order_price double,
primary key (order_detail_id)
);
create table order_management
(
order_id int not null auto_increment,
user_id int,
consignee_id int,
order_time datetime,
order_total double,
order_state varchar(10),
primary key (order_id)
);
create table user_info
(
user_id int not null auto_increment,
user_name varchar(50),
user_sex varchar(10),
user_phone varchar(11),
user_pw varchar(100),
user_type varchar(5) comment '1 普通用户 2 管理员',
primary key (user_id)
);
alter table consignee_management add constraint FK_Reference_1 foreign key (user_id)
references user_info (user_id) on delete restrict on update restrict;
alter table order_detail add constraint FK_Reference_4 foreign key (order_id)
references order_management (order_id) on delete restrict on update restrict;
alter table order_detail add constraint FK_Reference_5 foreign key (goods_id)
references goods_info (goods_id) on delete restrict on update restrict;
alter table order_management add constraint FK_Reference_2 foreign key (user_id)
references user_info (user_id) on delete restrict on update restrict;
alter table order_management add constraint FK_Reference_3 foreign key (consignee_id)
references consignee_management (consignee_id) on delete restrict on update restrict;