db_init.sql

条形码商品管理系统

--用户管理表
create table TabUserInfo
(UserID int primary key,
 UserName varchar(20),
 Password varchar(20),
 Role int,
 Status int,
 CreateTime datetime,
 StatusChangeTime datetime,
 LastLoginTime datetime);

--系统管理员
insert into TabUserInfo values(0, 'admin', '', 0, 0, 
    getdate(), getdate(), getdate());

create table TabTestEdControl
(TotalDays int,
 UsedDays int,
 LastLoginTime datetime);
 
insert into TabTestEdControl values(90, 0, getdate());

--货物信息表
create table TabGoodsInfo
(GoodsID varchar(40) primary key,
 GoodsType char(2),
 GoodsClass char(2),
 GoodsName varchar(100),
 SubjectPersons varchar(100),
 RepertoryAmount int,
 ImportUnitPrice float,
 PurchaseUnitPrice float,
 SpecialUnitPrice float,
 GoodsShelfID char(3),
 ShelfLayerID varchar(10),
 LayerCellID varchar(10),
 Creators varchar(160),
 LastCreator varchar(20),
 LastUpdateTime datetime,
 ConfirmFlage char(1));

--货物信息表测试数据
insert into TabGoodsInfo values('123', '01', '02', '七里香', '周杰伦', 
    23, 10, 15, 13.5, '001', '3', '2', 'admin', 'admin', getdate(), '1');

--库存货物价格变化信息
create table TabRepertoryGoodsPriceCurve
(GoodsID varchar(40) not null,
 IndexNum int not null,
 StartTime datetime,
 EndTime datetime,
 ImportUnitPrice float);
alter table TabRepertoryGoodsPriceCurve
       add primary key (GoodsID, IndexNum);

--库存货物价格变化信息测试数据
insert into TabRepertoryGoodsPriceCurve values('123', 1, getdate(), getdate(), 10);
 
--货物类型代码表
create table TabGoodsTypeCode
(GoodsType char(2) primary key,
 GoodsTypeName varchar(80));

--货物类型代码表测试数据
insert into TabGoodsTypeCode values('01', 'CD');
insert into TabGoodsTypeCode values('02', 'VCD');
insert into TabGoodsTypeCode values('03', 'DVD');

--货物分类代码表
create table TabGoodsClassCode
(GoodsClass char(2) primary key,
 GoodsClassName varchar(80));
 
--货物分类代码表测试数据
insert into TabGoodsClassCode values('01', '大陆经典');
insert into TabGoodsClassCode values('02', '港台经典');
insert into TabGoodsClassCode values('03', '欧美经典');
 
--货架标识代码表
create table TabGoodsShelfIDCode
(GoodsShelfID char(3) primary key,
 GoodsShelfIDName varchar(20));

--货架标识代码表测试数据
insert into TabGoodsShelfIDCode values('001', '新品一号架');
insert into TabGoodsShelfIDCode values('002', '经典一号架');
insert into TabGoodsShelfIDCode values('003', '经典二号架');

--进货货物明细表
create table TabGoodsImportGoods
(BillID int not null,
 GoodsID varchar(40) not null,
 GoodsName varchar(100),
 ImportAmount int,
 ImportUnitPrice float,
 CreatePerson varchar(20),
 CreateTime datetime,
 ConfirmFlage char(1));
alter table TabGoodsImportGoods
       add primary key (BillID, GoodsID);

--进货单信息表
create table TabGoodsImportInfo
(BillID int primary key,
 SendPersons varchar(100),
 InceptPersons varchar(100),
 TotalPrice float,
 ImportTime datetime,
 PaymentTime datetime);

--售货货物明细表
create table TabGoodsExportGoods
(BillID int not null,
 GoodsID varchar(40) not null,
 GoodsName varchar(100),
 ExportAmount int,
 ExportUnitPrice float,
 SalesPerson varchar(20),
 CreateTime datetime,
 ConfirmFlage char(1),
 MemberId int);
alter table TabGoodsExportGoods
       add primary key (BillID, GoodsID);

--售货单信息表
create table TabGoodsExportInfo
(BillID int primary key,
 MemberId int,
 SalesPersons varchar(100),
 TotalPrice float,
 Discount float,
 TotalDisPrice float,
 ExportTime datetime,
 PaymentTime datetime);
 
--会员信息表
create table TabMemberInfo
(MemberId int primary key,
 MemberName varchar(20),
 Discount float,
 CreateTime datetime);