期末大作業01
今天進行了期末大作業開發，是一個心理咨詢app，
以下是我的數據庫設計,只是當前階段的，后續可能還需要添加
-- 創建數據庫
CREATE DATABASE IF NOT EXISTS mental_health DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mental_health;
-- 用戶表
CREATE TABLE IF NOT EXISTS user (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
nickname VARCHAR(50) NOT NULL,
phone VARCHAR(20),
email VARCHAR(100),
avatar VARCHAR(255),
gender TINYINT COMMENT '0:未知,1:男,2:女',
role TINYINT NOT NULL DEFAULT 0 COMMENT '0:普通用戶,1:咨詢師',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
status TINYINT NOT NULL DEFAULT 1 COMMENT '0:禁用,1:啟用'
);
-- 聊天會話表
CREATE TABLE IF NOT EXISTS chat_session (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
counselor_id INT NOT NULL,
status TINYINT NOT NULL DEFAULT 1 COMMENT '1:進行中,0:已結束',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (counselor_id) REFERENCES user(id),
UNIQUE KEY uk_user_counselor (user_id, counselor_id)
);
-- 聊天消息表
CREATE TABLE IF NOT EXISTS chat_message (
id INT PRIMARY KEY AUTO_INCREMENT,
session_id INT NOT NULL,
sender_id INT NOT NULL,
sender_role TINYINT NOT NULL COMMENT '0:用戶,1:咨詢師',
content TEXT NOT NULL,
send_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_read TINYINT NOT NULL DEFAULT 0 COMMENT '0:未讀,1:已讀',
FOREIGN KEY (session_id) REFERENCES chat_session(id),
FOREIGN KEY (sender_id) REFERENCES user(id)
);
-- 咨詢師表
CREATE TABLE IF NOT EXISTS counselor (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
gender TINYINT NOT NULL COMMENT '1:男,2:女',
avatar VARCHAR(255),
phone VARCHAR(20),
email VARCHAR(100),
introduction TEXT COMMENT '個人簡介',
specialties VARCHAR(255) COMMENT '擅長領域，用逗號分隔',
education VARCHAR(255) COMMENT '教育背景',
certifications TEXT COMMENT '資質證書',
experience_years INT COMMENT '從業年限',
rating DOUBLE DEFAULT 5.0 COMMENT '評分',
review_count INT DEFAULT 0 COMMENT '評價數量',
consultation_methods VARCHAR(100) COMMENT '咨詢方式，如：語音,視頻,文字',
therapy_schools VARCHAR(255) COMMENT '治療流派',
price_per_hour DOUBLE NOT NULL COMMENT '每小時價格',
status TINYINT NOT NULL DEFAULT 1 COMMENT '0:禁用,1:啟用',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 快速咨詢申請表
CREATE TABLE IF NOT EXISTS quick_consultation_request (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
problem_description TEXT NOT NULL COMMENT '核心心理問題描述',
duration VARCHAR(50) COMMENT '問題持續時間',
preferred_method VARCHAR(50) COMMENT '偏好咨詢方式',
image_urls TEXT COMMENT '圖片URL，用逗號分隔',
status TINYINT NOT NULL DEFAULT 0 COMMENT '0:待匹配,1:已匹配,-1:已取消',
matched_counselor_id INT,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (matched_counselor_id) REFERENCES counselor(id)
);
-- 咨詢預約表
CREATE TABLE IF NOT EXISTS consultation_appointment (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
counselor_id INT NOT NULL,
consultation_type VARCHAR(50) NOT NULL COMMENT '咨詢類型：電話/圖文/視頻',
duration INT NOT NULL COMMENT '時長（分鐘）',
appointment_time DATETIME NOT NULL COMMENT '預約時間',
amount DOUBLE NOT NULL COMMENT '金額',
payment_status TINYINT NOT NULL DEFAULT 0 COMMENT '0:未支付,1:已支付,-1:支付失敗',
appointment_status TINYINT NOT NULL DEFAULT 0 COMMENT '0:待確認,1:已確認,2:已完成,-1:已取消',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (counselor_id) REFERENCES counselor(id)
);
-- 咨詢記錄表
CREATE TABLE IF NOT EXISTS consultation_record (
id INT PRIMARY KEY AUTO_INCREMENT,
appointment_id INT NOT NULL,
user_id INT NOT NULL,
counselor_id INT NOT NULL,
summary TEXT COMMENT '咨詢摘要',
feedback TEXT COMMENT '咨詢師反饋',
rating TINYINT COMMENT '評分(1-5星)',
review_content TEXT COMMENT '評價內容',
consultation_time DATETIME NOT NULL COMMENT '咨詢時間',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (appointment_id) REFERENCES consultation_appointment(id),
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (counselor_id) REFERENCES counselor(id)
);
-- 創建索引
CREATE INDEX idx_appointment_user ON consultation_appointment(user_id);
CREATE INDEX idx_appointment_counselor ON consultation_appointment(counselor_id);
CREATE INDEX idx_appointment_time ON consultation_appointment(appointment_time);
CREATE INDEX idx_record_user ON consultation_record(user_id);
CREATE INDEX idx_record_counselor ON consultation_record(counselor_id);
CREATE INDEX idx_quick_request_user ON quick_consultation_request(user_id);
浙公網安備 33010602011771號