dbcarsys.sql

汽车销售管理系统,进销存系统典范,值得初学者多多学习

/*-----------------------------------------------------------*/
/*			创建数据库			     */
/*-----------------------------------------------------------*/
use master
go
if exists(select * from sysdatabases where name='dbCarsys')
	drop database dbCarsys
go
create database dbCarsys
go
use dbCarsys
go

/*-----------------------------------------------------------*/
/*			客户信息表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblClient')
	drop table tblClient
go
create table tblClient
(
	cliCode		Varchar(10) primary key,
	cliName		Varchar(10) not null,
	cliSex		Bit not null,
	cliPid		Varchar(18) not null,
	cliBirthday	Datetime,
	cliIncName	Varchar(50),
	cliIncCharacter	Varchar(20),
	cliIncCalling	Varchar(20),
	cliHeadship	Varchar(20),
	cliTel		Varchar(13) not null,
	cliPoss		Varchar(6) not null,
	cliAdd		Varchar(100) not null
)
go

/*-----------------------------------------------------------*/
/*			代办项目表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblCommissionItem')
	drop table tblCommissionItem
go
create table tblCommissionItem
(
	comCode		Varchar(10) primary key,
	comName		Varchar(20) not null,
	comMoney	Money not null,
	comRemark	varchar(200)
)
go

/*-----------------------------------------------------------*/
/*			厂商信息表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblFactory')
	drop table tblFactory
go
create table tblFactory
(
	facCode		Varchar(10) primary key,
	facName		Varchar(100) not null,
	facLinkMan	Varchar(15) not null,
	facTel		Varchar(13) not null,
	facAdd		Varchar(50) not null,
	facPos		Varchar(6),
	facState	bit,
	facRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			车辆信息表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblCarType')
	drop table tblCarType
go
create table tblCarType
(
	ctCode		Varchar(20) primary key,
	ctName		Varchar(20) not null,
	ctType		Varchar(30) not null,
	ctPlace		Varchar(100) not null,
	ctCarFac	Varchar(100) not null,
	facCode		Varchar(10) foreign key references tblFactory(facCode),
	ctRePrice	Money not null,
	ctSellPrice	Money not null
)
go

/*-----------------------------------------------------------*/
/*			仓库信息表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblDepotInfo')
	drop table tblDepotInfo
go
create table tblDepotInfo
(
	diCode		Varchar(10) primary key,
	diName		Varchar(10) UNIQUE not null,
	diUser		Varchar(10) not null,
	diAdd		varchar(100) not null,
	diTel		Varchar(13) not null,
	diIsStop	nvarchar(1) not null
)
go

/*-----------------------------------------------------------*/
/*			员工表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblUser')
	drop table tblUser
go
create table tblUser
(
	uCode		Varchar(10) primary key,
	uPwd		Varchar(10) not null,
	uName		Varchar(10) not null,
	uPinYin		Varchar(50) not null,
	uSex		Bit not null,
	uPid		Varchar(18) not null,
	uBranch		Varchar(20) not null,
	uJob		Varchar(20) not null,
	uIncumbency 	datetime not null,
	uDimission 	datetime,
	uState		Tinyint not null,
	uTel		Varchar(13) not null,
	uAdd		Varchar(100)
)
go

/*-----------------------------------------------------------*/
/*			权限规则表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblAccess')
	drop table tblAccess
go
create table tblAccess
(
	uCode		Varchar(10) foreign key references tblUser(uCode),
	formName	Varchar(20),
	controlName	Varchar(20) constraint pk_tblAccess_uCode_formName_controlName primary key(ucode,formName,controlName),
	aaction		Bit,
	accInfo		varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			系统初始表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblOsInit')
	drop table tblOsInit
go
create table tblOsInit
(
	osiCode 	varchar(10) primary key,
	osiName		varchar(100) not null,
	osiAdd		varchar(100) not null,
	osiTel		varchar(100) not null,
	osiWeb		varchar(100) not null,
	osiInDepotCode	varchar(10) not null,
	osiMoveCode	varchar(10) not null,
	osiSellCode	varchar(10) not null,
	osiGatheringCode	 varchar(10) not null,
	osiCommissionCode	varchar(10) not null,
	osiPayCode	varchar(10) not null,
	osiPreCode	varchar(10) not null,
	osiStoCode	varchar(10) not null
)
go


/*-----------------------------------------------------------*/
/*			采购单表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblStock')
	drop table tblStock
go
create table tblStock
(
	stoCode		varchar(10) primary key,
	stoDate		datetime not null,
	ctCode		varchar(20) foreign key references tblCarType(ctCode) not null,
	stoState	Tinyint default 0,
	stoColor	varchar(20) not null,
	stoAdvance	Money default 0,
	stoPrice	Money not null,
	sotRemark	varchar(500),
	optCode		varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	varchar(10) foreign key references tblUser(uCode) not null,
	stoIdea		varchar(200),
)
go

/*-----------------------------------------------------------*/
/*			入库单表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblInDepot')
	drop table tblInDepot
go
create table tblInDepot
(
	indCode		Varchar(10) primary key,
	indDate		Varchar(20) not null,
	diCode		Varchar(10) foreign key references tblDepotInfo(diCode) not null,
	stoCode		Varchar(10) foreign key references tblStock(stoCode) not null,
	indColor	varchar(10) not null,
	indButtom	Varchar(20) not null,
	indEngine	Varchar(20) not null,
	indEli		Varchar(20) not null,
	indImport	Varchar(20) ,
	indVerify	Varchar(20) not null,
	indSelf		Varchar(20) not null,
	indKey		Varchar(20) not null,
	indKilm		Varchar(20) not null,
	indOutDate	Varchar(20) not null,
	indPrice	money	    not null,
	indTake		Varchar(20) not null,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode),
	indState	Tinyint     not null,
	indOutStore	datetime,
	indOutMan	varchar(10) foreign key references tblUser(uCode),
	indInfo		varchar(100),
	indRemark	varchar(100),
)
go

/*-----------------------------------------------------------*/
/*			移库单表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblMove')
	drop table tblMove
go
create table tblMove
(
	movCode		Varchar(10) primary key,
	movData		Varchar(10) default getDate(),
	indCode		Varchar(10) foreign key references tblInDepot(indCode) not null,
	formCode	Varchar(10) foreign key references tblDepotInfo(diCode) not null,
	toCode		Varchar(10) foreign key references tblDepotInfo(diCode) not null,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	movRemark	varchar(100),
)
go

/*-----------------------------------------------------------*/
/*			预售订单表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblPresell')
	drop table tblPresell
go
create table tblPresell
(
	preCode		Varchar(10) primary key,
	preDate		Datetime default getDate(),
	cliCode		Varchar(10) foreign key references tblClient(cliCode) not null,
	ctCode		Varchar(20) foreign key references tblCarType(ctCode) not null,
	ctColor		varchar(20) not null,
	preEarnest	Money,
	prePrice	Money,--新加   预订单价
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	preRemark	Varchar(200),
	preState	Tinyint default 0 not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	preIdea		Varchar(200),
)
go

/*-----------------------------------------------------------*/
/*			销售单表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblSell')
	drop table tblSell
go
create table tblSell
(
	selCode		Varchar(10) primary key,
	preCode		Varchar(10) foreign key references tblPresell(preCode),
	selDate		Datetime default getDate() not null,
	cliCode		Varchar(10) foreign key references tblClient(cliCode) not null,
	indCode		Varchar(10) foreign key references tblInDepot(indCode) not null,
	selWay		Varchar(20),
	selPurpose	Varchar(20),
	selPrice	Money not null,
	selRemark	Varchar(200),
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	selState	Tinyint default 0 not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	selIdea		Varchar(200)
)
go