82 lines
2.3 KiB
MySQL
82 lines
2.3 KiB
MySQL
|
-- 创建数据库
|
||
|
|
CREATE DATABASE IF NOT EXISTS equip_reserve DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
||
|
|
USE `equip_reserve`;
|
||
|
|
|
||
|
|
-- 用户表
|
||
|
|
CREATE TABLE users
|
||
|
|
(
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
||
|
|
password VARCHAR(255) NOT NULL,
|
||
|
|
name VARCHAR(50),
|
||
|
|
phone VARCHAR(20),
|
||
|
|
team_id BIGINT,
|
||
|
|
enabled BOOLEAN DEFAULT TRUE,
|
||
|
|
created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 角色表
|
||
|
|
CREATE TABLE roles
|
||
|
|
(
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
code VARCHAR(30) NOT NULL UNIQUE,
|
||
|
|
name VARCHAR(50) NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 用户-角色关联表
|
||
|
|
CREATE TABLE user_roles
|
||
|
|
(
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
role_id BIGINT NOT NULL,
|
||
|
|
PRIMARY KEY (user_id, role_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 团队表
|
||
|
|
CREATE TABLE teams
|
||
|
|
(
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
name VARCHAR(100) NOT NULL,
|
||
|
|
created_time DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 设备表
|
||
|
|
CREATE TABLE devices
|
||
|
|
(
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
name VARCHAR(100) NOT NULL,
|
||
|
|
usage_requirement TEXT,
|
||
|
|
location VARCHAR(255),
|
||
|
|
image_path VARCHAR(255),
|
||
|
|
status VARCHAR(20) NOT NULL default 'AVAILABLE',
|
||
|
|
team_id BIGINT NOT NULL,
|
||
|
|
device_admin_id BIGINT,
|
||
|
|
created_time DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 预约记录表
|
||
|
|
CREATE TABLE reservations
|
||
|
|
(
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
device_id BIGINT NOT NULL,
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
start_time DATE NOT NULL,
|
||
|
|
end_time DATE NOT NULL,
|
||
|
|
applicant_name VARCHAR(50) NOT NULL,
|
||
|
|
applicant_team VARCHAR(50),
|
||
|
|
applicant_contact VARCHAR(50),
|
||
|
|
status VARCHAR(30) NOT NULL,
|
||
|
|
device_admin_id BIGINT,
|
||
|
|
created_time DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 审批记录表
|
||
|
|
CREATE TABLE approvals
|
||
|
|
(
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
reservation_id BIGINT NOT NULL,
|
||
|
|
step TINYINT NOT NULL,
|
||
|
|
approver_id BIGINT NOT NULL,
|
||
|
|
decision TINYINT NOT NULL,
|
||
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|