基于 RagFlow 与 Dify 的医疗知识库自动化查询方案（数据篇）

MySQL 数据库设计

在实体关系建模的基础上，我们设计了如下 SQL 结构来构建关系型数据库。该方案包含主键、外键约束及必要的索引优化，确保数据的完整性与检索效率。

-- MySQL 8.0+ 建议配置
CREATE DATABASE med_kb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE med_kb;

-- =====================================================================
-- 通用设置：简化审计字段 & 约束规范
-- =====================================================================

-- 疾病表
CREATE TABLE diseases (
    disease_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL COMMENT '疾病名称（主称谓）',
    icd_code VARCHAR(50) UNIQUE COMMENT 'ICD-10 编码（唯一）',
    description TEXT COMMENT '疾病描述',
    etiology TEXT COMMENT '病因',
    diagnostic_criteria TEXT COMMENT '诊断标准',
    -- 审计字段
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- 约束：名称不得为空白；ICD 编码可空，但若不为空则需满足基础模式
    CONSTRAINT chk_disease_name_not_blank CHECK (TRIM(name) <> ''),
    CONSTRAINT chk_icd_code_pattern  (icd_code    icd_code REGEXP ),
    
    INDEX idx_disease_name (name),
    INDEX idx_disease_name_icd (name, icd_code),
    FULLTEXT INDEX ftx_disease_text (description, etiology, diagnostic_criteria)
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;


 symptoms (
    symptom_id   AUTO_INCREMENT,
    name ()  COMMENT ,
    description TEXT COMMENT ,
    body_system () COMMENT ,
    created_at    ,
    updated_at       ,
     chk_symptom_name_not_blank  ((name)  ),
    INDEX idx_symptom_name (name),
    INDEX idx_symptom_system (body_system),
    FULLTEXT INDEX ftx_symptom_text (description)
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;


 disease_symptom (
    disease_id  ,
    symptom_id  ,
    association_strength TINYINT UNSIGNED   COMMENT ,
    evidence_level ()   COMMENT ,
    source ()   COMMENT ,
    last_reviewed    COMMENT ,
     (disease_id, symptom_id),
     fk_ds_disease  (disease_id)  diseases(disease_id)   CASCADE   CASCADE,
     fk_ds_symptom  (symptom_id)  symptoms(symptom_id)   CASCADE   CASCADE,
    INDEX idx_ds_symptom (symptom_id),
    INDEX idx_ds_strength (association_strength)
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;


 medications (
    medication_id   AUTO_INCREMENT,
    generic_name ()  COMMENT ,
    brand_name ()   COMMENT ,
    ingredients TEXT COMMENT ,
    indications TEXT COMMENT ,
    dosage TEXT COMMENT ,
    side_effects JSON COMMENT ,
    created_at    ,
    updated_at       ,
     chk_med_generic_not_blank  ((generic_name)  ),
     chk_side_effects_json  (side_effects    JSON_VALID(side_effects)),
     KEY uk_med_generic_brand (generic_name, brand_name),
    INDEX idx_med_generic (generic_name),
    FULLTEXT INDEX ftx_med_text (ingredients, indications, dosage)
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;


 disease_medication (
    disease_id  ,
    medication_id  ,
    relation_type ENUM(,,,,)   COMMENT ,
    notes TEXT COMMENT ,
     (disease_id, medication_id),
     fk_dm_disease  (disease_id)  diseases(disease_id)   CASCADE   CASCADE,
     fk_dm_med  (medication_id)  medications(medication_id)   CASCADE   CASCADE,
    INDEX idx_dm_medication (medication_id),
    INDEX idx_dm_relation (relation_type)
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;


 treatment_plans (
    plan_id   AUTO_INCREMENT,
    name ()  COMMENT ,
    steps JSON COMMENT ,
    expected_effect TEXT COMMENT ,
    created_at    ,
    updated_at       ,
     chk_plan_name_not_blank  ((name)  ),
     chk_steps_json  (steps    JSON_VALID(steps)),
    INDEX idx_plan_name (name),
    FULLTEXT INDEX ftx_plan_text (expected_effect)
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;


 treatment_plan_disease (
    plan_id  ,
    disease_id  ,
     (plan_id, disease_id),
     fk_tpd_plan  (plan_id)  treatment_plans(plan_id)   CASCADE   CASCADE,
     fk_tpd_disease  (disease_id)  diseases(disease_id)   CASCADE   CASCADE,
    INDEX idx_tpd_disease (disease_id)
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;


 treatment_plan_medication (
    plan_id  ,
    medication_id  ,
    usage_notes TEXT COMMENT ,
     (plan_id, medication_id),
     fk_tpm_plan  (plan_id)  treatment_plans(plan_id)   CASCADE   CASCADE,
     fk_tpm_med  (medication_id)  medications(medication_id)   CASCADE   CASCADE,
    INDEX idx_tpm_med (medication_id)
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;


 entity_aliases (
    alias_id   AUTO_INCREMENT,
    entity_type ENUM(,,,) ,
    entity_id  ,
    alias () ,
     KEY uk_type_id_alias (entity_type, entity_id, alias),
    INDEX idx_alias (alias),
     chk_alias_not_blank  ((alias)  )
) ENGINEInnoDB  CHARSETutf8mb4 COMMENT;