一、数据库总体设计
1.1 设计原则
遵循第三范式
适当冗余以提高查询效率
考虑数据完整性和一致性
预留扩展字段
1.2 表关系图
二、数据库表结构
2.1 基础表
2.1.1 用户表(sys_user)
CREATE TABLE sys_user (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
username VARCHAR(50) NOT NULL COMMENT '用户名',
password VARCHAR(100) NOT NULL COMMENT '密码',
real_name VARCHAR(50) COMMENT '真实姓名',
phone VARCHAR(20) COMMENT '手机号',
email VARCHAR(100) COMMENT '邮箱',
department_id BIGINT COMMENT '部门ID',
position VARCHAR(50) COMMENT '职位',
user_type VARCHAR(20) COMMENT '用户类型(工程师/班组长/工区长/部门主管)',
face_feature TEXT COMMENT '人脸特征数据',
status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
created_time DATETIME COMMENT '创建时间',
updated_time DATETIME COMMENT '更新时间',
UNIQUE KEY uk_username (username)
) COMMENT '用户表';
2.1.2 部门表(sys_department)
CREATE TABLE sys_department (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
dept_name VARCHAR(100) NOT NULL COMMENT '部门名称',
parent_id BIGINT COMMENT '父部门ID',
level INT COMMENT '层级',
sort_order INT COMMENT '排序',
status TINYINT DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
created_time DATETIME COMMENT '创建时间',
updated_time DATETIME COMMENT '更新时间'
) COMMENT '部门表';
2.2 设备管理相关表
2.2.1 设备信息表(device_info)
CREATE TABLE device_info (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
device_code VARCHAR(50) NOT NULL COMMENT '设备编号',
device_name VARCHAR(100) NOT NULL COMMENT '设备名称',
device_type VARCHAR(50) NOT NULL COMMENT '设备类型(客服/机电/消防/电梯)',
model VARCHAR(100) COMMENT '设备型号',
manufacturer VARCHAR(200) COMMENT '生产厂家',
purchase_date DATE COMMENT '采购日期',
installation_date DATE COMMENT '安装日期',
warranty_period DATE COMMENT '保修期',
maintenance_type VARCHAR(20) COMMENT '维保方式(自维/托管/技服)',
status VARCHAR(20) COMMENT '设备状态(正常/故障/报废)',
location VARCHAR(200) COMMENT '安装位置',
department_id BIGINT COMMENT '所属部门ID',
specifications TEXT COMMENT '设备规格',
remark TEXT COMMENT '备注',
created_time DATETIME COMMENT '创建时间',
updated_time DATETIME COMMENT '更新时间',
UNIQUE KEY uk_device_code (device_code)
) COMMENT '设备信息表';
2.2.2 设备变更记录表(device_change_log)
CREATE TABLE device_change_log (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
device_id BIGINT NOT NULL COMMENT '设备ID',
change_type VARCHAR(50) NOT NULL COMMENT '变更类型',
change_content TEXT COMMENT '变更内容',
operator_id BIGINT COMMENT '操作人ID',
created_time DATETIME COMMENT '创建时间'
) COMMENT '设备变更记录表';
2.2.3 设备巡检项目表(device_inspection_items)
CREATE TABLE device_inspection_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
device_type VARCHAR(50) NOT NULL COMMENT '设备类型',
item_name VARCHAR(200) NOT NULL COMMENT '巡检项目名称',
item_description TEXT COMMENT '项目描述',
check_method TEXT COMMENT '检查方法',
standard_value VARCHAR(200) COMMENT '标准值',
created_time DATETIME COMMENT '创建时间',
updated_time DATETIME COMMENT '更新时间'
) COMMENT '设备巡检项目表';
2.3 工单管理相关表
2.3.1 工单主表(work_order)
CREATE TABLE work_order (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
order_no VARCHAR(50) NOT NULL COMMENT '工单编号',
device_id BIGINT NOT NULL COMMENT '设备ID',
order_type VARCHAR(20) NOT NULL COMMENT '工单类型(故障/巡检/保养/检测)',
status VARCHAR(20) NOT NULL COMMENT '工单状态(待处理/处理中/待验收/已完成)',
priority VARCHAR(20) COMMENT '优先级(低/中/高/紧急)',
description TEXT COMMENT '工单描述',
engineer_id BIGINT COMMENT '工程师ID',
start_time DATETIME COMMENT '开始时间',
end_time DATETIME COMMENT '完成时间',
repair_method TEXT COMMENT '维修方法',
repair_result TEXT COMMENT '维修结果',
created_time DATETIME COMMENT '创建时间',
updated_time DATETIME COMMENT '更新时间',
UNIQUE KEY uk_order_no (order_no)
) COMMENT '工单主表';
-- 修改工单表，添加满意度评分和完成时间字段
ALTER TABLE work_order
ADD COLUMN satisfaction_score INT COMMENT '满意度评分',
ADD COLUMN completion_time DATETIME COMMENT '最终完成时间',
ADD COLUMN approval_stage VARCHAR(50) COMMENT '审核阶段（班组长/工区长/部门主管）';
2.3.2 工单审核记录表(work_order_approval)
CREATE TABLE work_order_approval (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
order_id BIGINT NOT NULL COMMENT '工单ID',
approver_id BIGINT NOT NULL COMMENT '审核人ID',
approval_type VARCHAR(20) COMMENT '审核类型(班组长审核/工区长审核/部门主管审核)',
approval_status VARCHAR(20) COMMENT '审核状态(待审核/通过/驳回)',
approval_time DATETIME COMMENT '审核时间',
approval_opinion TEXT COMMENT '审核意见',
satisfaction_score INT COMMENT '满意度评分',
face_verify_status TINYINT COMMENT '人脸验证状态(0:未验证,1:已验证)',
created_time DATETIME COMMENT '创建时间'
) COMMENT '工单审核记录表';
ALTER TABLE work_order_approval
ADD COLUMN signature TEXT COMMENT '电子签名数据',
ADD COLUMN signature_time DATETIME COMMENT '签名时间';
2.3.3 工单照片表(work_order_photo)
CREATE TABLE work_order_photo (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
order_id BIGINT NOT NULL COMMENT '工单ID',
photo_type VARCHAR(20) COMMENT '照片类型(处理前/处理后)',
photo_url VARCHAR(500) NOT NULL COMMENT '照片URL',
photo_time DATETIME COMMENT '拍摄时间',
watermark_info VARCHAR(200) COMMENT '水印信息',
quality_check_status VARCHAR(20) COMMENT '质量检查状态',
quality_score DECIMAL(5,2) COMMENT '图片质量分数',
blur_degree DECIMAL(5,2) COMMENT '模糊度',
created_time DATETIME COMMENT '创建时间'
) COMMENT '工单照片表';
2.3.4 备件更换记录表(parts_replacement)
CREATE TABLE parts_replacement (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
order_id BIGINT NOT NULL COMMENT '工单ID',
part_name VARCHAR(100) NOT NULL COMMENT '备件名称',
part_model VARCHAR(100) COMMENT '备件型号',
quantity INT NOT NULL COMMENT '更换数量',
replacement_time DATETIME COMMENT '更换时间',
operator_id BIGINT COMMENT '操作人ID',
created_time DATETIME COMMENT '创建时间'
) COMMENT '备件更换记录表';
2.3.5 巡检记录明细表(inspection_detail)
CREATE TABLE inspection_detail (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
order_id BIGINT NOT NULL COMMENT '工单ID',
item_id BIGINT NOT NULL COMMENT '巡检项目ID',
check_result VARCHAR(50) COMMENT '检查结果(正常/异常)',
abnormal_description TEXT COMMENT '异常描述',
created_time DATETIME COMMENT '创建时间'
) COMMENT '巡检记录明细表';
2.4 通知消息相关表
2.4.1 系统通知表(sys_notification)
CREATE TABLE sys_notification (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
user_id BIGINT NOT NULL COMMENT '接收用户ID',
title VARCHAR(200) NOT NULL COMMENT '通知标题',
content TEXT COMMENT '通知内容',
notify_type VARCHAR(20) COMMENT '通知类型(系统消息/短信通知/待办提醒)',
read_status TINYINT DEFAULT 0 COMMENT '读取状态(0:未读,1:已读)',
created_time DATETIME COMMENT '创建时间'
) COMMENT '系统通知表';
2.4.2 待办任务表(todo_task)
CREATE TABLE todo_task (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
user_id BIGINT NOT NULL COMMENT '用户ID',
task_type VARCHAR(50) NOT NULL COMMENT '任务类型',
task_content TEXT COMMENT '任务内容',
related_id BIGINT COMMENT '关联ID(工单ID等)',
status TINYINT DEFAULT 0 COMMENT '状态(0:待处理,1:已处理)',
deadline DATETIME COMMENT '截止时间',
priority INT DEFAULT 0 COMMENT '优先级',
created_time DATETIME COMMENT '创建时间',
updated_time DATETIME COMMENT '更新时间'
) COMMENT '待办任务表';
2.5 系统配置表
2.5.1 系统参数配置表(sys_config)
CREATE TABLE sys_config (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
param_key VARCHAR(100) NOT NULL COMMENT '参数键',
param_value VARCHAR(500) NOT NULL COMMENT '参数值',
param_desc VARCHAR(500) COMMENT '参数描述',
created_time DATETIME COMMENT '创建时间',
updated_time DATETIME COMMENT '更新时间',
UNIQUE KEY uk_param_key (param_key)
) COMMENT '系统参数配置表';
三、索引设计
3.1 主要索引列表
sys_user: username(唯一索引)
device_info: device_code(唯一索引)
work_order: order_no(唯一索引)
work_order: device_id, status(复合索引)
work_order_approval: order_id, approval_status(复合索引)
sys_notification: user_id, read_status(复合索引)
todo_task: user_id, status(复合索引)
四、数据字典
4.1 通用状态码
0: 禁用/未处理
1: 启用/已处理
4.2 工单状态
PENDING: 待处理
PROCESSING: 处理中
WAITING_ACCEPTANCE: 待验收
COMPLETED: 已完成
REJECTED: 已驳回
4.3 设备类型
CUSTOMER_SERVICE: 客服设备
MECHANICAL: 机电设备
FIRE_CONTROL: 消防设备
ELEVATOR: 电梯设备
4.4 维保方式
SELF: 自维
OUTSOURCE: 托管
TECHNICAL: 技服
五、注意事项
所有表都包含审计字段(created_time, updated_time)
重要操作都需要记录日志
关键字段都建立了索引
图片存储使用独立的文件存储系统，数据库只存储URL
人脸特征数据需要加密存储
六、扩展建议
可以考虑添加设备保养计划表
可以添加设备操作手册表
可以添加设备维修历史统计表
可以添加工单评价详情表
七、性能优化建议
对于大表建议进行分区
历史数据可以考虑归档
频繁查询的数据可以使用缓存
图片等大文件建议使用对象存储