database.txt

基于Struts框架的企业进销存管理系统

create sequence product_sequence;
create sequence manufactory_seq;
create sequence customer_seq;
create sequence product_in_seq;
create sequence product_in_list_seq;
create sequence product_sale_seq;
create sequence product_sale_list_seq;
create sequence ware_house_seq;
create sequence house_store_seq;
create sequence employee_seq;
create sequence admin_seq;
create sequence product_type_seq;
create sequence product_unit_seq;
create sequence employee_type_seq;

//产品类别表
create table product_type
(
 id number(4) primary key,
 name varchar2(20)
)

//产品单位表
create table product_unit
(id number(4)primary key,
 name varchar2(20)
)

//商品表
create table product
(
 id number(10)primary key,
 p_no varchar2(15),
 p_name varchar2(80),
 p_size varchar2(30),
 madewhere varchar2(40),
 in_price number(20,2),
 sale_price number(20,2),
 lowest_price number(20,2),
 type_id number(4)references product_type(id),
 unit_id number(4)references product_unit(id)
)

//供货商表
create table manufactory
(
 id number(10) primary key,
 name varchar2(80),
 short_name varchar2(60),
 address varchar2(80),
 post_code varchar2(6),
 area varchar2(40),
 area_code varchar2(6),
 type varchar2(40),
 phone varchar2(12),
 bank_name varchar2(30),
 bank_code varchar2(6),
 account_num varchar2(20)
)

//客户表
create table customer
(
 id number(10) primary key,
 name varchar2(80),
 short_name varchar2(60),
 chief varchar2(20),
 address varchar2(80),
 post_code varchar2(6),
 area varchar2(40),
 area_code varchar2(6),
 phone varchar2(12),
 bank_name varchar2(40),
 bank_code varchar2(6),
 account_num varchar2(20)
)

//进货单
create table product_in
(
 id number(10) primary key,
 manufactory_id number(10) references manufactory(id),
 in_date date,
 busy_person varchar2(20),
 operation_person varchar2(20),
 check_person varchar2(20),
 house_person varchar2(20),
 cost number(20,2),
 order_no varchar2(20)
)
//进货单明细
create table product_in_list
(
 id number(10) primary key,
 product_in_id number(10) references product_in(id),
 product_id number(10) references product(id),
in_price number(20,2),
 product_num number(20),
 cost number(20,2)
)

//销售单
create table product_sale
(
 id number(10) primary key,
 customer_id number(10) references customer(id),
 sale_date date,
 busy_person varchar2(20),
 operation_person varchar2(20),
 house_person varchar2(20),
 cost number(20,2),
 order_no varchar2(20)
)

//销售单明细
create table product_sale_list
(
 id number(10) primary key,
 product_sale_id number(10) references product_sale(id),
 product_id number(10) references product(id),
 sale_price number(20,2),
 product_num number(20),
 cost number(20,2)
)

///仓库表
create table ware_house
(
 id number(10) primary key,
 name varchar2(20),
 house_person varchar2(20),
 phone varchar2(15),
 top number(20),
 low number(20)
)
//商品库存表
create table house_store
(
 id number(10) primary key,
 product_id number(10) references product(id),
 house_id number(10) references ware_house(id),
 product_num number(30)
)

//员工类型表
create table employee_type
(
 id number(4) primary key,
 name varchar2(10)
)
//员工表
create table employee
(
 id number(10) primary key,
 name varchar2(20),
 sex varchar2(10),
 age number(5),
 type number(4) references employee_type(id)
)

//管理员表
create table admin
(
 id number(4) primary key,
 name varchar2(20) not null,
 password varchar2(20) not null,
 type number(4)
)

insert into product_type
 values(product_type_seq.nextval,'电脑');

insert into product_type
 values(product_type_seq.nextval,'数码');

insert into product_type
 values(product_type_seq.nextval,'手机');

insert into product_type
 values(product_type_seq.nextval,'MP3');

insert into product_type
 values(product_type_seq.nextval,'U盘');

insert into product_unit
 values(product_unit_seq.nextval,'台');

insert into product_unit
 values(product_unit_seq.nextval,'部');

insert into product_unit
 values(product_unit_seq.nextval,'盒');

insert into product_unit
 values(product_unit_seq.nextval,'箱');

insert into product_unit
 values(product_unit_seq.nextval,'车皮');

insert into product_unit
 values(product_unit_seq.nextval,'个');

insert into product_unit
 values(product_unit_seq.nextval,'打');
 
 
 insert into ware_house values(1,'东仓','老张','0379-6521770',10000000,1000);
insert into ware_house values(2,'西仓','老李','0379-6521770',10000000,1000);
insert into ware_house values(3,'南仓','老赵','0379-6521770',10000000,1000);
insert into ware_house values(4,'北仓','老黄','0379-6521770',10000000,1000);