zrd-nb666/campus_health_app/docs/diagrams/report-database-er-new.html

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>数智健调 - 数据库ER图</title>
    <script src="https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Microsoft YaHei', sans-serif;
            max-width: 1200px;
            margin: 0 auto;
            padding: 20px;
            background: #f5f5f5;
        }
        h1 { color: #333; text-align: center; }
        h2 { color: #666; border-bottom: 2px solid #3b82f6; padding-bottom: 10px; }
        .mermaid { background: white; padding: 20px; border-radius: 8px; margin: 20px 0; }
        .description { background: white; padding: 15px; border-radius: 8px; margin: 10px 0; }
        table { width: 100%; border-collapse: collapse; margin: 10px 0; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background: #3b82f6; color: white; }
        tr:nth-child(even) { background: #f9f9f9; }
    </style>
</head>
<body>
    <h1>🗄️ 数智健调 - 数据库ER图</h1>
    
    <h2>实体关系图</h2>
    <div class="mermaid">
erDiagram
    USERS ||--o{ WEIGHT_RECORDS : "has"
    USERS ||--o| WEIGHT_GOALS : "has"
    USERS ||--o{ TAGS : "creates"
    WEIGHT_RECORDS }o--o{ TAGS : "tagged_with"
    USERS ||--o{ ANOMALY_LOGS : "has"
    
    USERS {
        varchar id PK "用户ID(UUID)"
        varchar phone UK "手机号"
        varchar nickname "昵称"
        varchar school_id FK "学校ID"
        varchar grade "年级"
        boolean is_under_14 "是否未满14岁"
        varchar parent_phone "家长手机号"
        timestamp created_at "创建时间"
        timestamp updated_at "更新时间"
    }
    
    WEIGHT_RECORDS {
        varchar id PK "记录ID(UUID)"
        varchar user_id FK "用户ID"
        date record_date "记录日期"
        time measurement_time "测量时间"
        decimal weight "体重(kg)"
        decimal body_fat "体脂率(%)"
        decimal muscle_mass "肌肉含量(kg)"
        enum measurement_condition "测量条件"
        text notes "备注"
        timestamp created_at "创建时间"
    }
    
    WEIGHT_GOALS {
        varchar id PK "目标ID(UUID)"
        varchar user_id FK "用户ID"
        decimal target_weight "目标体重(kg)"
        decimal target_body_fat "目标体脂率(%)"
        date target_date "目标日期"
        decimal start_weight "起始体重(kg)"
        date start_date "开始日期"
        decimal weekly_target "每周目标减重量"
        enum status "状态"
        timestamp created_at "创建时间"
    }
    
    TAGS {
        varchar id PK "标签ID(UUID)"
        varchar user_id FK "用户ID"
        varchar tag_name "标签名称"
        enum tag_type "类型"
        varchar color "颜色代码"
        timestamp created_at "创建时间"
    }
    
    ANOMALY_LOGS {
        varchar id PK "日志ID(UUID)"
        varchar user_id FK "用户ID"
        enum anomaly_type "异常类型"
        enum severity "严重程度"
        text message "提示信息"
        date detected_date "检测日期"
        json related_record_ids "相关记录ID"
        boolean is_read "是否已读"
        timestamp created_at "创建时间"
    }
    </div>
    
    <h2>数据表详细设计</h2>
    
    <h3>1. 用户表 (users)</h3>
    <table>
        <tr><th>字段名</th><th>类型</th><th>约束</th><th>说明</th></tr>
        <tr><td>id</td><td>VARCHAR(36)</td><td>PRIMARY KEY</td><td>用户ID（UUID）</td></tr>
        <tr><td>phone</td><td>VARCHAR(20)</td><td>UNIQUE, NOT NULL</td><td>手机号</td></tr>
        <tr><td>nickname</td><td>VARCHAR(50)</td><td>NOT NULL</td><td>昵称</td></tr>
        <tr><td>school_id</td><td>VARCHAR(36)</td><td>-</td><td>学校ID</td></tr>
        <tr><td>grade</td><td>VARCHAR(20)</td><td>-</td><td>年级</td></tr>
        <tr><td>created_at</td><td>TIMESTAMP</td><td>DEFAULT CURRENT_TIMESTAMP</td><td>创建时间</td></tr>
    </table>
    
    <h3>2. 体重记录表 (weight_records)</h3>
    <table>
        <tr><th>字段名</th><th>类型</th><th>约束</th><th>说明</th></tr>
        <tr><td>id</td><td>VARCHAR(36)</td><td>PRIMARY KEY</td><td>记录ID（UUID）</td></tr>
        <tr><td>user_id</td><td>VARCHAR(36)</td><td>FOREIGN KEY</td><td>用户ID</td></tr>
        <tr><td>record_date</td><td>DATE</td><td>NOT NULL</td><td>记录日期</td></tr>
        <tr><td>weight</td><td>DECIMAL(5,2)</td><td>NOT NULL, CHECK(30-200)</td><td>体重(kg)</td></tr>
        <tr><td>body_fat</td><td>DECIMAL(4,2)</td><td>NOT NULL, CHECK(5-60)</td><td>体脂率(%)</td></tr>
        <tr><td>muscle_mass</td><td>DECIMAL(5,2)</td><td>NOT NULL, CHECK(10-100)</td><td>肌肉含量(kg)</td></tr>
        <tr><td>measurement_condition</td><td>ENUM</td><td>-</td><td>fasting/after_meal</td></tr>
        <tr><td>notes</td><td>TEXT</td><td>-</td><td>备注</td></tr>
    </table>
    
    <h3>3. 体重目标表 (weight_goals)</h3>
    <table>
        <tr><th>字段名</th><th>类型</th><th>约束</th><th>说明</th></tr>
        <tr><td>id</td><td>VARCHAR(36)</td><td>PRIMARY KEY</td><td>目标ID（UUID）</td></tr>
        <tr><td>user_id</td><td>VARCHAR(36)</td><td>FOREIGN KEY</td><td>用户ID</td></tr>
        <tr><td>target_weight</td><td>DECIMAL(5,2)</td><td>NOT NULL</td><td>目标体重(kg)</td></tr>
        <tr><td>target_date</td><td>DATE</td><td>NOT NULL</td><td>目标日期</td></tr>
        <tr><td>start_weight</td><td>DECIMAL(5,2)</td><td>NOT NULL</td><td>起始体重(kg)</td></tr>
        <tr><td>start_date</td><td>DATE</td><td>NOT NULL</td><td>开始日期</td></tr>
        <tr><td>status</td><td>ENUM</td><td>DEFAULT 'active'</td><td>active/completed/abandoned</td></tr>
    </table>
    
    <div class="description">
        <h3>设计说明</h3>
        <ul>
            <li><strong>主键设计</strong>：所有表使用UUID作为主键，便于分布式扩展</li>
            <li><strong>外键约束</strong>：使用ON DELETE CASCADE确保数据一致性</li>
            <li><strong>索引优化</strong>：在常用查询字段上建立索引提升性能</li>
            <li><strong>软删除</strong>：使用deleted_at字段实现软删除功能</li>
        </ul>
    </div>
</body>
</html>