Intelligent_Health_Evaluation_System_4_Civilian_Health_Monitoring/数据库框架.md

数据库表结构定义

user_info 表

用于存储管理员用户的基本信息、认证凭据和权限。注意：此表只存储管理员用户，患者用户存储在 patient_info 表中。

表结构 (SQL DDL)

CREATE TABLE user_info (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    hashed_password VARCHAR(255) NOT NULL,
    department VARCHAR(100),
    position VARCHAR(100),
    is_admin BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

字段说明

字段名	类型	约束	说明
id	SERIAL	PRIMARY KEY	管理员唯一标识，自增主键。
username	VARCHAR(50)	UNIQUE, NOT NULL	管理员用户名，用于登录，必须唯一。
email	VARCHAR(255)	UNIQUE, NOT NULL	管理员邮箱，必须唯一。
hashed_password	VARCHAR(255)	NOT NULL	使用 bcrypt 哈希算法加密后的密码。
department	VARCHAR(100)		管理员所属部门。
position	VARCHAR(100)		管理员职位。
is_admin	BOOLEAN	DEFAULT TRUE	标识用户是否为管理员，默认为 TRUE。
created_at	TIMESTAMPTZ	DEFAULT NOW()	记录创建时间。
updated_at	TIMESTAMPTZ	DEFAULT NOW()	记录最后更新时间。

示例数据

为了方便测试，可以插入以下数据：

-- 密码: admin123
INSERT INTO user_info (username, email, hashed_password, department, position, is_admin) 
VALUES ('admin', 'admin@example.com', '$2b$12$EixZaYVK1eCjG4n6b5D1zuR8yA5d1E0n.3Jd9G.Yk.H8zJ.p7.wI.', '系统管理', '管理员', TRUE);

-- 密码: manager123
INSERT INTO user_info (username, email, hashed_password, department, position, is_admin) 
VALUES ('manager', 'manager@example.com', '$2b$12$xG.Vn2G7f2wJdDGkQxGjA.w0q.K6.Qj3S.h2Z/d.aY5q.w2K.X5U.', '医疗管理', '主管', TRUE);

patient_info 表

用于存储患者用户的基本信息和认证凭据。患者通过扫码注册，使用就诊号+身份证后六位登录。

表结构 (SQL DDL)

CREATE TABLE patient_info (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) UNIQUE NOT NULL,
    medical_record_number VARCHAR(50) UNIQUE NOT NULL,
    id_last_six VARCHAR(6) NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    dominant_hand VARCHAR(10) NOT NULL,
    diagnosis TEXT,
    dob DATE,
    height_cm INTEGER,
    weight_kg DECIMAL(5,2),
    contact VARCHAR(20),
    inpatient_number VARCHAR(50),
    bed_number VARCHAR(20),
    education_level VARCHAR(50),
    education_years INTEGER,
    remarks TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

字段说明

字段名	类型	约束	说明
id	SERIAL	PRIMARY KEY	患者唯一标识，自增主键。
user_id	VARCHAR(50)	UNIQUE, NOT NULL	系统生成的用户ID，用于内部标识。
medical_record_number	VARCHAR(50)	UNIQUE, NOT NULL	就诊号，用作登录用户名，必须唯一。
id_last_six	VARCHAR(6)	NOT NULL	身份证后六位，用作登录密码。
name	VARCHAR(100)	NOT NULL	患者姓名。
gender	VARCHAR(10)	NOT NULL	性别（男/女）。
dominant_hand	VARCHAR(10)	NOT NULL	利手（左/右）。
diagnosis	TEXT		诊断信息。
dob	DATE		出生日期（格式：YYYY-MM-DD）。
height_cm	INTEGER		身高（厘米）。
weight_kg	DECIMAL(5,2)		体重（公斤）。
contact	VARCHAR(20)		联系方式。
inpatient_number	VARCHAR(50)		住院号（可选）。
bed_number	VARCHAR(20)		床号（可选）。
education_level	VARCHAR(50)		教育程度（可选）。
education_years	INTEGER		教育年数（可选）。
remarks	TEXT		备注信息（可选）。
created_at	TIMESTAMPTZ	DEFAULT NOW()	记录创建时间。
updated_at	TIMESTAMPTZ	DEFAULT NOW()	记录最后更新时间。

示例数据

INSERT INTO patient_info (
    user_id, medical_record_number, id_last_six, name, gender, dominant_hand, 
    diagnosis, dob, height_cm, weight_kg, contact, inpatient_number, 
    bed_number, education_level, education_years, remarks
) VALUES (
    'user-abc123', 'J0012345', '123456', '张三', '男', '右',
    '无', '1960-01-15', 175, 70.5, '13800138000', 'Z67890',
    '503', '本科', 16, '测试患者'
);

logs_metadata 表

用于记录所有历史日志表的元信息。管理员通过查询此表来获取历史日志列表。此表在管理员登录时由系统自动更新。

表结构 (SQL DDL)

CREATE TABLE logs_metadata (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

字段说明

字段名	类型	约束	说明
id	SERIAL	PRIMARY KEY	元数据记录的唯一标识。
table_name	VARCHAR(100)	UNIQUE, NOT NULL	实际存储日志数据的表名。
description	TEXT		对该日志的描述。
created_at	TIMESTAMPTZ	DEFAULT NOW()	记录创建的时间，默认为当前时间。
updated_at	TIMESTAMPTZ	DEFAULT NOW()	记录最后更新时间。

示例数据

INSERT INTO logs_metadata (table_name, description, created_at) VALUES 
('log_20240315_1', '2024年3月15日第一次交互日志', '2024-03-15T14:30:00Z'),
('log_20240315_2', '2024年3月15日第二次交互日志', '2024-03-15T16:30:00Z');

动态测试日志表 (log_... 表)

这种表由系统在管理员登录时动态创建，用于存储一个特定测试批次的所有用户测试数据。表名格式为 log_YYYYMMDD_N (例如 log_20240401_1)。

表结构 (SQL DDL)

CREATE TABLE log_YYYYMMDD_N (
    log_id SERIAL PRIMARY KEY,
    user_id VARCHAR(255) UNIQUE NOT NULL,
    medical_record_number VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'initialized',
    gait_analysis JSONB,
    facial_expression INTEGER,
    eye_tracking INTEGER,
    handwriting INTEGER,
    speech_analysis INTEGER,
    semantic_analysis INTEGER,
    raw_data_path VARCHAR(255)
);

字段说明

字段名	类型	说明
log_id	SERIAL	本次测试日志的唯一ID，主键。
user_id	VARCHAR(255)	患者的用户ID，关联 patient_info 表。
medical_record_number	VARCHAR(100)	患者就诊号。
name	VARCHAR(100)	患者姓名。
created_at	TIMESTAMPTZ	记录创建时间。
status	VARCHAR(50)	测试日志的状态。默认为 initialized (已初始化)。流程: initialized -> processing (数据处理中) -> pending_review (待专家审核) -> completed (已完成确认)。
gait_analysis	JSONB	存储步态分析结果的JSON对象。
facial_expression	INTEGER	存储面部表情的UPDRS评级（0-4），可为空。
eye_tracking	INTEGER	存储眼动功能的UPDRS评级（0-4），可为空。
handwriting	INTEGER	存储手写功能的UPDRS评级（0-4），可为空。
speech_analysis	INTEGER	存储言语功能的UPDRS评级（0-4），可为空。
semantic_analysis	INTEGER	存储认知与语义连贯性的评级（0-4），可为空。
raw_data_path	VARCHAR(255)	指向原始时序数据文件（如CSV）的路径。

示例数据

-- 此表示例数据为概念性展示，实际数据结构嵌套在JSONB字段中
INSERT INTO log_20240401_1 (user_id, medical_record_number, name, status, gait_analysis, facial_expression) VALUES
('user-abc123', 'J0012345', '张三', 'completed', '{
    "procedural_events": [{"event": "stand_up", "frame": 85}],
    "walk_segments": [{"segment_id": 1, "gait_speed": 1.2}]
}', 2);

questions 表

用于存储视频问卷的所有问题。

表结构 (SQL DDL)

CREATE TABLE questions (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    content TEXT NOT NULL,
    required BOOLEAN DEFAULT TRUE,
    max_length INTEGER,
    -- 可以增加一个 category 字段，用于关联特定的视频或场景
    category VARCHAR(100)
);

字段说明

字段名	类型	说明
id	SERIAL	问题的唯一ID。
type	VARCHAR(50)	问题类型，例如 text。
content	TEXT	问题的主要内容。
required	BOOLEAN	该问题是否为必答题，默认为 TRUE。
max_length	INTEGER	答案的最大长度限制。
category	VARCHAR(100)	问题分类，可用于从特定类别的题库中抽题。

示例数据

INSERT INTO questions (type, content, required, max_length, category) VALUES
('text', '请描述视频中展示的主要问题是什么？', TRUE, 500, 'general'),
('text', '您认为应该如何解决这个问题？', TRUE, 1000, 'general'),
('text', '您对这个解决方案有什么建议？', FALSE, 800, 'general'),
('text', '视频中的场景让您联想到了什么？', TRUE, 500, 'emotion'),
('text', '您是否在现实生活中遇到过类似的情况？', FALSE, 1000, 'experience'),
('text', '如果您是视频中的主角，您会怎么做？', TRUE, 1000, 'decision'),
('text', '这个事件对您的触动是什么？', TRUE, 800, 'emotion'),
('text', '请用三个词来形容您的观看感受。', TRUE, 100, 'emotion');

用户类型说明

管理员用户 (user_info表)

• 注册方式: 管理后台创建
• 登录方式: 用户名 + 密码
• 权限: 管理员权限，可查看所有测试记录
• 特点: 使用bcrypt加密密码，支持邮箱验证

患者用户 (patient_info表)

• 注册方式: 扫码自助注册
• 登录方式: 就诊号 + 身份证后六位
• 权限: 普通用户权限，可进行测试
• 特点: 使用就诊号作为用户名，身份证后六位作为密码