tablesql.sql

来自「简单的餐饮软件部分源码」· SQL 代码 · 共 235 行

use snack
go

/*create table sntest
(fileid int not null primary key,
 usedate nchar(12) not null,
 timesnu int not null)
go*/
create table snuser
(userid nchar(20) not null Primary key,
 username nchar(100) not null,
 userpassword nchar(8) null)
go

insert snuser(userid,username,userpassword) Values('2008','Admin','19760914')
go


create table sncard /*系统用刷卡登录*/ 
(cardid nchar(20) not null primary key,
 cardnu nchar(100) not null,
 userid nchar(20) not null,
 username nchar(100) not null)
go

create table snmodel
(userid nchar(20) not null primary key
 constraint foreignkey_logoid
 foreign key references snuser(userid)
 on delete cascade
 not for replication,
 model1 nchar(3) not null,
 model2 nchar(3) not null,
 model3 nchar(3) not null,
 model4 nchar(3) not null,
 model5 nchar(3) not null,
 model6 nchar(3) not null, 
 model7 nchar(3) not null,
 model8 nchar(3) not null,
 model9 nchar(3) not null)
go

insert snmodel(userid,model1,model2,model3,model4,model5,model6,model7,model8) Values('2008','En','En','En','En','En','En','En','En');

create table snsystemsetup /*系统权限设定表*/
(cboxpath nvarchar(200) null,
 paymore nchar(5) not null,
 casher nchar(5) not null
)
go

create table snrecorder  /*系统操作记录表*/
(userid nchar(20) not null,
 username nchar(100) not null,
 recdate nchar(10) not null,
 rectime nchar(10) not null,
 hostname nchar(20) not null,
 recmodel nchar(200) not null,
 recmemo nvarchar(200) null)
go

create table sndlmeal
(mealdlid nchar(6) not null primary key,
 mealname nchar(20) not null)
go

create table snxlmeal /*菜式小类*/
(mealdlid nchar(6) not null,
 mealxlid nchar(6) not null primary key,
 xlname nchar(20) not null)
go

create table snmeal/*菜式资料表*/
(mealxlid nchar(6) not null,
 mealid nchar(6) not null primary key,
 mealname nchar(40) not null,
 unit nchar(6) not null,
 mealprice numeric(6,2) not null,
 mealtcflag nchar(3) not null,    
 mealprinter varchar(100) null,
 mealdlid nchar(6) not null)
go

create table snsealout     /*沽清表*/
(mealid nchar(6) not null,
 sealoutdate nchar(10) not null,
 sealtime nchar(10) not null)
go

create table sntempmeal  /*点菜临时表*/
(tablename nchar(6) not null,
 mealid nchar(6) not null,
 mealname nchar(40) not null,
 mealprice numeric(6,2) not null,
 unit nchar(8) not null,
 mealcount int not null,
 mealkw  nvarchar(200) null,
 mealtime char(10) not null,
 userid nchar(20) not null,
 idname nchar(100) not null,
 tcflag nchar(3) not null,
 txid nchar(6)  null,
 tctime nchar(8) null,
 pkflag nchar(3) not null)
go

create table snsalemeal /*点菜下单后正式点菜表*/
(billid nchar(30) not null,
 mealid nchar(6) null,
 mealname nchar(40) not null,
 mealprice numeric(6,2) not null,
 unit nchar(8) not null,
 mealcount int not null,
 mealkw  nvarchar(200) null,
 mealtime char(10) not null,
 userid nchar(20) not null,
 idname nchar(100) not null,
 tcflag nchar(3) not null,
 txid nchar(6)  null,
 tctime nchar(8) null,
 tableno nchar(6) not null,
 pkflag nchar(3) not null)
go

create table snsalebill /*结账记录表 */
(billid nchar(30) not null,
 billflag nvarchar(2) not null,
 tablename nchar(6) not null,
 mancount int not null,
 cpaycash numeric(6,2) not null,
 billpay numeric(6,2) not null,
 paydate nchar(10) not null,
 paytime nchar(8) not null,
 changwid nvarchar(12) null,
 changw nchar(3) null,
 userid nchar(20) not null,
 idname nchar(100) not null
 )
go

create table snbillnorec /*账单单号表*/
(billdate nchar(8) not null  primary key,
 billrecnu nchar(7) not null)
go 

create table paybillmemo/*账单付款明细*/
(billid nchar(30) not null,
 paycode nchar(6) not null,
 payname nvarchar(10) not null,
 paycash numeric(6,2) not null,
 paycardid nvarchar(22) null,
 cardcash numeric(6,2) null,
 paytime nvarchar(14) not null)
go

create table snkeyname
(mealdlid nchar(6) not null,
 keynameid nchar(3) not null primary key,
 keyidname nvarchar(20) not null)
go

create table snkeymeal /*菜式对应的按键*/
(keynameid1 nchar(3) null,
 keynameid2 nchar(3) null,
 keynameid3 nchar(3) null,
 mealid nchar(6) not null,
 mealname nchar(40) not null,
 unit nchar(6) not null,
 mealprice numeric(6,2) not null,
 suite nchar(3) not null,
 mpriter nvarchar(100) null)
go

create table snsutiemeal /*套餐记录表*/
(mealids nchar(6) not null,
 mealid nchar(6) not null,
 mealname nchar(40) not null,
 unit nchar(6) not null,
 mealnu int not null,
 mealprice numeric(6,2) not null,
 mealgd nchar(3) not null
)
go

create table snredcash  /*备用金记录表*/
(cashdate nchar(10) not null,
 cashnu int null,
 cashtime nchar(8) not null,
 userid nchar(20) not null,
 idname nchar(100) not null
 )
go

create table snmealkw  /*菜式口味*/
(mealid nchar(6) not null,
 kwid nchar(6) not null,
 kwidname nchar(20) not null)
go

create table snkwrec/*口味资料表*/
(mealdxid nchar(6) not null,
 kwid nchar(6) not null primary key,
 kwidname nchar(20) not null)
go

create table snkwmemo
(kwid nchar(6) not null,
 kwmemoid nchar(3) not null primary key,
 kwname nchar(20) not null)
go

create table snmealkwmemo
(tableno nchar(6) not null,
 mealid nchar(6) not null,
 kwmemoid nchar(3) not null,
 kwname nchar(20) not null)
go

create table snmorepay
(payid nchar(3) not null primary key,
 payname nvarchar(20) not null)
go

create table sncreatebillno
(opendate nchar(10) not null,
 tableid nvarchar(6) not null primary key,
 billno nvarchar(6) not null)
go

create table snunbumoney
(indate nchar(12) not null primary key,
 muchcash numeric(6,2) not null,
 recid nchar(20) not null,
 idname nvarchar(100) null)
go