create database zhouhui
go
use zhouhui
go
create table customer
(
customerID int primary key,
customerName varchar(50) not null,
address varchar(100) null,
linkMan char(20) null,
tel char(20) null,
fax char(20) null,
zipCode char(10) null,
customerRemark text null
)
go
create table feeder /*供货单位信息表*/
(
feederID int not null primary key, /*供货单位编号（主键）*/
address varchar(100) null, /*单位地址*/
linkMan char(20) null, /*联系人姓名*/
tel char(20) null, /*联系电话*/
fax char(20) null, /*传真号码*/
zipCode char(10) null, /*邮政编码*/
feederRemark text null, /*备注信息*/
)
go
/*建立库存信息数据表*/
create table storage /*库存信息数据表*/
(
storageID int not null primary key, /*库存信息编号（主键)*/
/* foreign key productID references product(productID), /*货品编号（外键)*/ */
depotName varchar(50) not null, /*仓库名称*/
productName varchar(50) not null, /*货品名称*/
spec varchar(50) null, /*货品规格*/
unit varchar(50) null, /*计量单位*/
)
/*建立出库信息数据表*/
create table sell /*出库信息数据表*/
(
sellID int not null primary key, /*出库信息编号（主键)*/
/*foreign key productID references product(productID), /*货品编号（外键)*/ */
customerName varchar(50) not null, /*收货单位名称*/
depotName varchar(50) not null, /*仓库名称*/
productName varchar not null, /*货品名称*/
quantity float not null, /*货品数量*/
unitPrice float not null, /*货品单价*/
payment float null, /*总付款*/
sellDate datetime null, /*出库日期*/
)
go
/*建立入库信息数据表*/
create table stock /*入库信息数据表*/
(
stockID int not null primary key, /*入库信息编号(主键)*/
/* foreign key productID references product(productID), /*货品编号（外键)*/*/
feederName varchar(50) not null, /*供货单位名称*/
depotName varchar(50) not null, /*仓库名称*/
productName varchar(50) not null, /*货品名称*/
quantity float not null, /*货品数量*/
unitPrice float not null, /*货品单价*/
stockDate datetime null, /*入库日期*/
)
/*建立仓库信息数据表*/
create table depot /*仓库信息表*/
(
depotID int not null primary key, /*仓库编号（主键/自动编号)*/
depotName varchar not null, /*仓库名称*/
address varchar not null, /*仓库位置*/
principal varchar not null, /*仓库负责人*/
)
create table product /*货品信息数据表*/
(
productID int not null primary key, /*货品编号（主键/自动编号)*/
customerID int, /*收货单位编号（外键）*/
feederID int, /*供货单位编号（外键）*/
storageID int, /*库存信息编号（外键)*/
depotID int , /*仓库编号（外键)*/
productName varchar(50) not null, /*货品名称*/
productClass varchar(50) not null, /*货品分类*/
productSpec varchar(50) not null, /*货品规格*/
unit varchar(50) null, /*计量单位*/
minSto float not null, /*底线库存*/
maxSto float not null /*高线库存*/
foreign key ([customerID]) references [dbo].[customer] ([customerID]),
foreign key ([feederID]) references [dbo].[feeder] ([feederID]),
foreign key([storageID]) references [storage]([storageID]),
foreign key([depotID]) references [depot]([depotID])
)
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=786834
