hotelbook-JavaWeb/src/sql/sql.sql at master · primary-code-test/hotelbook-JavaWeb · GitHub

Repository navigation

History

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

-- 建立hotelbook数据库

CREATE SCHEMA `hotelbook` DEFAULT CHARACTER SET utf8 ;

-- 建立登录表

CREATE TABLE `hotelbook`.`login` (

`loginId` INT NOT NULL AUTO_INCREMENT COMMENT '登录内部标示，主键、自动增长。',

`loginName` VARCHAR(45) NOT NULL COMMENT '登录用户名，前台唯一性的标识。不能重复。',

`loginPwd` VARCHAR(45) NOT NULL COMMENT '登录用户的密码，不能为空。',

`loginNickName` VARCHAR(45) NULL COMMENT '登录用户的昵称' ,

`loginAdmin` INT NULL COMMENT '权限，默认最高权限是0',

PRIMARY KEY (`loginId`),

UNIQUE INDEX `loginName_UNIQUE` (`loginName` ASC));

-- 为login表插入默认的管理员用户

INSERT INTO `hotelbook`.`login` (`loginName`,`loginPwd`,`loginNickName`,`loginAdmin`) VALUE ("root","eySvyLyA5UjWbE5/9yFxxQ==","管理员",0);

-- 建立日志表

CREATE TABLE `hotelbook`.`logInfo` (

`logId` INT NOT NULL AUTO_INCREMENT COMMENT '日志，主键、自动增长。',

`logName` VARCHAR(45) NOT NULL COMMENT '日志项目',

`loginId` INT NOT NULL COMMENT '用户外键 login表字段值',

`loginName` VARCHAR(45) NULL COMMENT '用户名称',

`logDate` VARCHAR(45) NULL COMMENT '日志时间' ,

PRIMARY KEY (`logId`),

CONSTRAINT `fk_logInfo_1`

FOREIGN KEY (`loginId`)

REFERENCES `hotelbook`.`login` (`loginId`)

ON DELETE NO ACTION

ON UPDATE NO ACTION);

-- 创建主体数据表

-- 核心数据库内容

-- 将不使用存储过程，仅仅用到外键/外联

-- 数据维护主要依靠后端维护

-- 楼层信息

CREATE TABLE `hotelbook`.`floorInfo` (

`floorId` INT NOT NULL AUTO_INCREMENT COMMENT '楼层编号',

`floorName` VARCHAR(45) NULL COMMENT '楼层名称',

PRIMARY KEY (`floorId`));

-- 客房类型

CREATE TABLE `hotelbook`.`roomType` (

`typeId` VARCHAR(45) NOT NULL COMMENT '类型编号',

`typeName` VARCHAR(45) NULL COMMENT '类型名称',

`price` VARCHAR(20) NULL COMMENT '价格',

`splicPrice` VARCHAR(20) NULL COMMENT '拼房价格',

`exceedance` INT NULL COMMENT '可超预定数',

`isSplice` VARCHAR(10) NULL COMMENT '是否可拼房',

PRIMARY KEY (`typeId`));

-- 客房信息

CREATE TABLE `hotelbook`.`roomInfo` (

`roomId` VARCHAR(45) NOT NULL COMMENT '客房编号',

`typeId` VARCHAR(45) NOT NULL COMMENT '类型编号',

`floorId` INT NOT NULL COMMENT '楼层编号',

`ratedNum` INT NULL COMMENT '额定人数',

`bedNum` INT NULL COMMENT '床数',

`roomDescription` VARCHAR(45) NULL COMMENT '客房描述',

`remark` VARCHAR(100) NULL COMMENT '备注',

`status` VARCHAR(10) NULL COMMENT '状态',

`isSplice` VARCHAR(10) NULL COMMENT '是否可拼房',

PRIMARY KEY (`roomId`),

INDEX `fk_roomInfo_1_idx` (`typeId` ASC),

INDEX `fk_roomInfo_2_idx` (`floorId` ASC),

CONSTRAINT `fk_roomInfo_1`

FOREIGN KEY (`typeId`)

REFERENCES `hotelbook`.`roomType` (`typeId`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_roomInfo_2`

FOREIGN KEY (`floorId`)

REFERENCES `hotelbook`.`floorInfo` (`floorId`)

ON DELETE NO ACTION

ON UPDATE NO ACTION);

-- 预订单

CREATE TABLE `hotelbook`.`orderInfo` (

`orderId` VARCHAR(45) NOT NULL COMMENT '预定单号',

`orderName` VARCHAR(45) NULL COMMENT '预定人',

`orderPhone` VARCHAR(45) NULL COMMENT '联系电话',

`orderIDcard` VARCHAR(18) NULL COMMENT '身份证号',

`typeId` VARCHAR(45) NOT NULL COMMENT '客房类型',

`arrireDate` VARCHAR(45) NULL COMMENT '抵店时间',

`leaveDate` VARCHAR(45) NULL COMMENT '离店时间',

`orderState` VARCHAR(20) NULL COMMENT '单据状态',

`checkNum` VARCHAR(45) NULL COMMENT '入住人数',

`roomId` VARCHAR(45) NULL COMMENT '客房编号',

`price` VARCHAR(20) NULL COMMENT '客房价格',

`checkPrice` VARCHAR(20) NULL COMMENT '入住价格',

`discount` INT NULL COMMENT '折扣',

`discountReason` VARCHAR(60) NULL COMMENT '折扣原因',

`addBed` VARCHAR(10) NULL COMMENT '是否加床',

`addBedPrice` VARCHAR(20) NULL COMMENT '加床价格',

`orderMoney` VARCHAR(20) NULL COMMENT '预收款',

`remark` VARCHAR(500) NULL COMMENT '备注',

`operatorId` VARCHAR(45) NULL COMMENT '操作员',

PRIMARY KEY (`orderId`),

INDEX `fk_orderInfo_1_idx` (`typeId` ASC),

CONSTRAINT `fk_orderInfo_1`

FOREIGN KEY (`typeId`)

REFERENCES `hotelbook`.`roomType` (`typeId`)

ON DELETE NO ACTION

ON UPDATE NO ACTION);

-- 入住单

CREATE TABLE `hotelbook`.`checkInInfo` (

`checkId` VARCHAR(45) NOT NULL COMMENT '入住单号',

`orderId` VARCHAR(45) NULL COMMENT '预定单号',

`checkName` VARCHAR(45) NULL COMMENT '入住人',

`checkPhone` VARCHAR(45) NULL COMMENT '联系电话',

`checkIDcard` VARCHAR(18) NULL COMMENT '身份证号',

`typeId` VARCHAR(45) NOT NULL COMMENT '客房类型',

`arrireTime` VARCHAR(45) NULL COMMENT '抵店时间',

`leaveTime` VARCHAR(45) NULL COMMENT '离店时间',

`checkState` VARCHAR(20) NULL COMMENT '单据状态',

`checkNum` INT NULL COMMENT '入住人数',

`roomId` VARCHAR(45) NOT NULL COMMENT '客房编号',

`price` VARCHAR(20) NULL COMMENT '客房价格',

`checkPrice` VARCHAR(20) NULL COMMENT '入住价格',

`discount` INT NULL COMMENT '折扣',

`discountReason` VARCHAR(60) NULL COMMENT '折扣原因',

`addBed` VARCHAR(10) NULL COMMENT '是否加床',

`addBedPrice` VARCHAR(20) NULL COMMENT '加床价格',

`orderMoney` VARCHAR(20) NULL COMMENT '预收款',

`money` VARCHAR(20) NULL COMMENT '应收账款',

`isCheck` VARCHAR(10) NULL COMMENT '是否结账',

`checkMoney` VARCHAR(20) NULL COMMENT '结账金额',

`checkDate` VARCHAR(45) NULL COMMENT '结账日期',

`remark` VARCHAR(500) NULL COMMENT '备注',

`operatorId` VARCHAR(45) NULL COMMENT '操作员',

PRIMARY KEY (`checkId`),

INDEX `fk_checkInfo_1_idx` (`typeId` ASC),

INDEX `fk_checkInfo_2_idx` (`roomId` ASC),

CONSTRAINT `fk_checkInfo_1`

FOREIGN KEY (`typeId`)

REFERENCES `hotelbook`.`roomType` (`typeId`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_checkInfo_2`

FOREIGN KEY (`roomId`)

REFERENCES `hotelbook`.`roomInfo` (`roomId`)

ON DELETE NO ACTION

ON UPDATE NO ACTION);

-- 账单明细

CREATE TABLE `hotelbook`.`billInfo` (

`billId` INT NOT NULL COMMENT '账单编号',

`checkId` VARCHAR(45) NOT NULL COMMENT '入住单号',

`costMoney` VARCHAR(20) NULL COMMENT '消费金额',

`costDate` VARCHAR(45) NULL COMMENT '消费时间',

`remark` VARCHAR(100) NULL COMMENT '备注',

PRIMARY KEY (`billId`),

INDEX `fk_billInfo_1_idx` (`checkId` ASC),

CONSTRAINT `fk_billInfo_1`

FOREIGN KEY (`checkId`)

REFERENCES `hotelbook`.`checkInInfo` (`checkId`)

ON DELETE NO ACTION

ON UPDATE NO ACTION);

-- 权限管理

CREATE TABLE `hotelbook`.`authInfo` (

`authId` INT NOT NULL AUTO_INCREMENT COMMENT '权限编号',

`authItem` VARCHAR(45) NULL COMMENT '权限项',

`isRead` VARCHAR(45) NULL COMMENT '可读',

`isWrite` VARCHAR(45) NULL COMMENT '可写',

`isChange` VARCHAR(45) NULL COMMENT '可改',

`isDelete` VARCHAR(45) NULL COMMENT '可删',

PRIMARY KEY (`authId`));

hotelbook-JavaWeb/src/sql/sql.sql at master · primary-code-test/hotelbook-JavaWeb · GitHub

Repository navigation

Expand file tree

Breadcrumbs

sql.sql

Latest commit

History

Breadcrumbs

sql.sql

File metadata and controls