92 lines
2.5 KiB
MySQL
92 lines
2.5 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
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 初始角色数据
|
||
|
|
INSERT INTO roles (code, name)
|
||
|
|
VALUES ('ADMIN', '系统管理员'),
|
||
|
|
('LEADER', '团队负责人'),
|
||
|
|
('DEVICE_ADMIN', '设备管理员'),
|
||
|
|
('USER', '普通用户');
|