749 lines
29 KiB
Markdown
749 lines
29 KiB
Markdown
# Data Model — MySQL 数据建模
|
||
|
||
> 定义数据库表结构、关系和设计约定。AI Agent 在生成 Migration 和 Model 时参考本文档。
|
||
|
||
---
|
||
|
||
## 1. 设计原则
|
||
|
||
- 主键统一使用 `BIGINT UNSIGNED AUTO_INCREMENT`
|
||
- 字符集 `utf8mb4`，排序规则 `utf8mb4_unicode_ci`
|
||
- 所有表包含 `created_at`, `updated_at`, `deleted_at` (软删除)
|
||
- 外键字段命名 `<table_singular>_id`，必须有索引
|
||
- 金额字段使用 `DECIMAL(10,2)`
|
||
- 状态字段使用 `VARCHAR(20)` 或 `TINYINT`（避免 ENUM）
|
||
- 时间字段使用 `TIMESTAMP`
|
||
- 排序字段使用 `INT UNSIGNED DEFAULT 0`
|
||
|
||
## 2. 命名规范
|
||
|
||
### 2.1 表命名规则
|
||
|
||
**表名必须以所属模块名作为前缀**（snake_case），格式：`<module>_<entity_plural>`。
|
||
|
||
| 模块 | 前缀 | 说明 |
|
||
|------|------|------|
|
||
| 用户与权限 | `auth_` | 用户、角色、菜单、OAuth 等 |
|
||
| 案例核心 | `case_` | 案例主表、图库、文件、分类等 |
|
||
| 设计师 | `designer_` | 设计师档案、奖项、标签关联等 |
|
||
| 运营内容 | `operation_` | 轮播图、专题、标签字典等 |
|
||
| 用户互动 | `interaction_` | 收藏、点赞、评论、关注、订阅等 |
|
||
| 日志 | `log_` | 登录日志、操作日志、下载日志等 |
|
||
| 安全风控 | `security_` | 黑名单、风控事件、会话等 |
|
||
| 系统配置 | `system_` | 系统参数配置等 |
|
||
|
||
> **规则说明**：
|
||
> - 同一业务域下的所有表使用相同前缀，避免不同模块表名冲突
|
||
> - 多对多关联表同样需要加模块前缀：`<module>_<a>_belongs_<b>`
|
||
> - 跨模块关联的中间表，以**主体模块**的前缀命名
|
||
|
||
### 2.2 通用命名约定
|
||
|
||
| 类型 | 规范 | 示例 |
|
||
|------|------|------|
|
||
| 表名 | `<module>_` 前缀 + snake_case 复数 | `case_cases`, `auth_users` |
|
||
| 字段名 | snake_case | `total_building_area` |
|
||
| 主键 | `id` | `BIGINT UNSIGNED` |
|
||
| 外键 | `<singular>_id` | `case_id`, `designer_id` |
|
||
| 索引 | `idx_<table>_<cols>` | `idx_case_cases_status` |
|
||
| 唯一索引 | `uk_<table>_<cols>` | `uk_case_cases_code` |
|
||
| 多对多关联表 | `<module>_<a>_belongs_<b>` | `case_case_belongs_tag` |
|
||
| 布尔字段 | `is_<adj>` | `is_featured`, `is_active` |
|
||
| 计数缓存 | `<noun>_count` | `view_count`, `like_count` |
|
||
|
||
### 2.3 各模块表名对照
|
||
|
||
| 当前表名 | 所属模块 | 规范表名 |
|
||
|----------|----------|----------|
|
||
| `users` | 用户与权限 | `auth_users` |
|
||
| `oauth_bindings` | 用户与权限 | `auth_oauth_bindings` |
|
||
| `roles` | 用户与权限 | `auth_roles` |
|
||
| `menus` | 用户与权限 | `auth_menus` |
|
||
| `user_belongs_role` | 用户与权限 | `auth_user_belongs_role` |
|
||
| `role_belongs_menu` | 用户与权限 | `auth_role_belongs_menu` |
|
||
| `categories` | 案例核心 | `case_categories` |
|
||
| `tags` | 运营内容 | `operation_tags` |
|
||
| `cases` | 案例核心 | `case_cases` |
|
||
| `case_images` | 案例核心 | `case_images`（已有前缀） |
|
||
| `case_files` | 案例核心 | `case_files`（已有前缀） |
|
||
| `case_belongs_tag` | 案例核心 | `case_belongs_tag`（已有前缀） |
|
||
| `case_team_members` | 案例核心 | `case_team_members`（已有前缀） |
|
||
| `designers` | 设计师 | `designer_profiles` |
|
||
| `designer_awards` | 设计师 | `designer_awards`（已有前缀） |
|
||
| `designer_belongs_tag` | 设计师 | `designer_belongs_tag`（已有前缀） |
|
||
| `banners` | 运营内容 | `operation_banners` |
|
||
| `topics` | 运营内容 | `operation_topics` |
|
||
| `topic_belongs_case` | 运营内容 | `operation_topic_belongs_case` |
|
||
| `favorites` | 用户互动 | `interaction_favorites` |
|
||
| `likes` | 用户互动 | `interaction_likes` |
|
||
| `comments` | 用户互动 | `interaction_comments` |
|
||
| `follows` | 用户互动 | `interaction_follows` |
|
||
| `newsletter_subscriptions` | 用户互动 | `interaction_newsletter_subscriptions` |
|
||
| `user_login_logs` | 日志 | `log_user_logins` |
|
||
| `admin_operation_logs` | 日志 | `log_admin_operations` |
|
||
| `download_logs` | 日志 | `log_downloads` |
|
||
| `user_sessions` | 安全风控 | `security_user_sessions` |
|
||
| `security_risk_events` | 安全风控 | `security_risk_events`（已有前缀） |
|
||
| `security_blacklists` | 安全风控 | `security_blacklists`（已有前缀） |
|
||
| `system_configs` | 系统配置 | `system_configs`（已有前缀） |
|
||
|
||
---
|
||
|
||
## 3. ER 关系总览
|
||
|
||
```
|
||
用户与权限
|
||
─────────
|
||
users ←→ user_belongs_role ←→ roles
|
||
│ │
|
||
└→ oauth_bindings role_belongs_menu ←→ menus
|
||
|
||
案例核心
|
||
────────
|
||
categories ←── cases ──→ case_images
|
||
│ └──→ case_files
|
||
│ └──→ case_belongs_tag ←── tags
|
||
│ └──→ case_team_members ──→ designers
|
||
│ │
|
||
│ └→ designer_awards
|
||
│ └→ designer_belongs_tag ←── tags
|
||
│
|
||
└──→ favorites ←── users
|
||
└──→ likes ←── users
|
||
└──→ comments ←── users
|
||
|
||
运营内容
|
||
────────
|
||
banners (独立)
|
||
topics ←→ topic_belongs_case ←→ cases
|
||
|
||
会话与安全
|
||
──────────
|
||
users ──→ user_sessions
|
||
users ──→ security_risk_events
|
||
security_blacklists (独立)
|
||
|
||
日志与系统
|
||
──────────
|
||
user_login_logs
|
||
admin_operation_logs
|
||
download_logs
|
||
system_configs
|
||
newsletter_subscriptions
|
||
```
|
||
|
||
---
|
||
|
||
## 4. 核心数据表
|
||
|
||
### 4.1 用户与权限域
|
||
|
||
#### `users` — 统一用户表
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK, AUTO_INCREMENT | |
|
||
| `user_type` | VARCHAR(20) | NOT NULL, DEFAULT 'user' | user / admin |
|
||
| `username` | VARCHAR(50) | NULL, UNIQUE | 登录账号 |
|
||
| `phone` | VARCHAR(20) | NULL, UNIQUE | 手机号 |
|
||
| `password` | VARCHAR(255) | NULL | bcrypt 哈希 |
|
||
| `enterprise_user_id` | VARCHAR(100) | NULL, UNIQUE | 企业平台用户唯一标识 |
|
||
| `source` | VARCHAR(20) | NOT NULL, DEFAULT 'local' | local / enterprise / wechat / qq |
|
||
| `is_enterprise_linked` | TINYINT | NOT NULL, DEFAULT 0 | 是否已绑定企业账号 |
|
||
| `nickname` | VARCHAR(50) | NULL | 显示昵称 |
|
||
| `avatar` | VARCHAR(500) | NULL | 头像 URL |
|
||
| `role_label` | VARCHAR(50) | NULL | 展示角色标签（如 Architect） |
|
||
| `previous_password_hash` | VARCHAR(255) | NULL | 上次密码哈希（防重复使用） |
|
||
| `agree_at` | TIMESTAMP | NULL | 用户同意协议时间 |
|
||
| `agreement_version` | VARCHAR(30) | NULL | 同意时协议版本 |
|
||
| `status` | TINYINT | NOT NULL, DEFAULT 1 | 1=启用, 0=禁用 |
|
||
| `deactivation_requested_at` | TIMESTAMP | NULL | 注销申请时间（冷静期起始） |
|
||
| `scheduled_deletion_at` | TIMESTAMP | NULL | 计划删除时间（冷静期结束） |
|
||
| `last_login_at` | TIMESTAMP | NULL | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
| `deleted_at` | TIMESTAMP | NULL | |
|
||
|
||
索引：`uk_users_username`, `uk_users_phone`, `uk_users_enterprise_user_id`, `idx_users_user_type`, `idx_users_status`
|
||
|
||
#### `oauth_bindings` — 第三方登录绑定
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users | |
|
||
| `provider` | VARCHAR(20) | NOT NULL | wechat / qq / enterprise |
|
||
| `provider_user_id` | VARCHAR(100) | NOT NULL | 第三方平台用户 ID |
|
||
| `access_token` | VARCHAR(500) | NULL | |
|
||
| `refresh_token` | VARCHAR(500) | NULL | |
|
||
| `expires_at` | TIMESTAMP | NULL | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
索引：`uk_oauth_provider_uid (provider, provider_user_id)`, `idx_oauth_user_id`
|
||
|
||
#### `roles` — 角色表
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `name` | VARCHAR(50) | NOT NULL, UNIQUE | 角色标识：super_admin / content_ops / reviewer |
|
||
| `display_name` | VARCHAR(50) | NOT NULL | 超级管理员 / 内容运营 / 审核员 |
|
||
| `description` | VARCHAR(200) | NULL | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
#### `menus` — 菜单/权限表
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `parent_id` | BIGINT UNSIGNED | DEFAULT 0 | 树形结构 |
|
||
| `name` | VARCHAR(50) | NOT NULL | 权限标识 `case:create` |
|
||
| `display_name` | VARCHAR(50) | NOT NULL | 菜单显示名 |
|
||
| `type` | VARCHAR(10) | NOT NULL | menu / button |
|
||
| `path` | VARCHAR(200) | NULL | 前端路由路径 |
|
||
| `icon` | VARCHAR(50) | NULL | 图标名 |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `is_visible` | TINYINT | DEFAULT 1 | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
#### `user_belongs_role` — 用户-角色关联
|
||
|
||
| 字段 | 类型 | 约束 |
|
||
|------|------|------|
|
||
| `user_id` | BIGINT UNSIGNED | PK(联合), FK → users |
|
||
| `role_id` | BIGINT UNSIGNED | PK(联合), FK → roles |
|
||
|
||
#### `role_belongs_menu` — 角色-菜单关联
|
||
|
||
| 字段 | 类型 | 约束 |
|
||
|------|------|------|
|
||
| `role_id` | BIGINT UNSIGNED | PK(联合), FK → roles |
|
||
| `menu_id` | BIGINT UNSIGNED | PK(联合), FK → menus |
|
||
|
||
---
|
||
|
||
### 4.2 案例核心域
|
||
|
||
#### `categories` — 建筑类型分类
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `name` | VARCHAR(50) | NOT NULL, UNIQUE | 别墅 / 民宿 / 室内 / 景观 / 农村自建房 / 庭院 |
|
||
| `slug` | VARCHAR(50) | NOT NULL, UNIQUE | villa / homestay / interior / landscape |
|
||
| `icon` | VARCHAR(50) | NULL | |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `case_count` | INT UNSIGNED | DEFAULT 0 | 缓存引用计数 |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
#### `tags` — 统一标签字典
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `type` | VARCHAR(20) | NOT NULL | style / feature / specialty |
|
||
| `name` | VARCHAR(50) | NOT NULL | 现代 / 落地窗 / 参数化设计 |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `reference_count` | INT UNSIGNED | DEFAULT 0 | 被引用次数 |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
索引：`uk_tags_type_name (type, name)`, `idx_tags_type`
|
||
|
||
#### `cases` — 案例主表
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `category_id` | BIGINT UNSIGNED | FK → categories | 建筑类型 |
|
||
| `title` | VARCHAR(200) | NOT NULL | 项目名称 |
|
||
| `code` | VARCHAR(30) | NOT NULL, UNIQUE | 项目编号 P-2024-8839 |
|
||
| `cover_image` | VARCHAR(500) | NULL | 封面图 URL |
|
||
| `status` | VARCHAR(20) | NOT NULL, DEFAULT 'draft' | draft / pending / published / archived |
|
||
| `is_featured` | TINYINT | DEFAULT 0 | 是否精选 |
|
||
| `featured_sort` | INT UNSIGNED | DEFAULT 0 | 精选排序 |
|
||
| — **户型布局** — | | | |
|
||
| `layout` | VARCHAR(50) | NULL | 3室2厅3卫 |
|
||
| `floor_height` | VARCHAR(50) | NULL | 3.6/3.3/3.3m |
|
||
| — **用地条件** — | | | |
|
||
| `site_width` | VARCHAR(50) | NULL | 面宽 ≥ 10.5m |
|
||
| `site_depth` | VARCHAR(50) | NULL | 进深 ≥ 12.2m |
|
||
| `site_area` | DECIMAL(10,2) | NULL | 占地面积 m² |
|
||
| `lighting_restriction` | VARCHAR(100) | NULL | 采光限制 |
|
||
| `site_shape` | VARCHAR(50) | NULL | 用地形状 |
|
||
| — **规模造价** — | | | |
|
||
| `floors_above` | TINYINT UNSIGNED | NULL | 地上层数 |
|
||
| `floors_below` | TINYINT UNSIGNED | NULL | 地下层数 |
|
||
| `cost_min` | DECIMAL(12,2) | NULL | 造价预估下限（万元） |
|
||
| `cost_max` | DECIMAL(12,2) | NULL | 造价预估上限（万元） |
|
||
| `total_building_area` | DECIMAL(10,2) | NULL | 建筑总面积 m² |
|
||
| `bedroom_count` | TINYINT UNSIGNED | NULL | 卧室数量 |
|
||
| `bedroom_note` | VARCHAR(100) | NULL | 含1主卧套房 |
|
||
| — **平面功能** — | | | |
|
||
| `stair_type` | VARCHAR(100) | NULL | 楼梯类型 |
|
||
| `special_space` | VARCHAR(200) | NULL | 特色空间 / 农村特色 |
|
||
| `bedroom_config` | VARCHAR(200) | NULL | 卧室配置 |
|
||
| `leisure_space` | VARCHAR(200) | NULL | 休闲空间 |
|
||
| `kitchen_dining` | VARCHAR(200) | NULL | 厨房餐厅 |
|
||
| `bathroom_config` | VARCHAR(100) | NULL | 卫生间配置 |
|
||
| — **外观风格** — | | | |
|
||
| `architecture_style` | VARCHAR(100) | NULL | 建筑风格 |
|
||
| `roof_type` | VARCHAR(100) | NULL | 屋顶形式 |
|
||
| `facade_material` | VARCHAR(200) | NULL | 外立面材料 |
|
||
| — **结构与性能** — | | | |
|
||
| `structure_type` | VARCHAR(100) | NULL | 结构类型 |
|
||
| `foundation_type` | VARCHAR(100) | NULL | 基础类型 |
|
||
| `layout_logic` | VARCHAR(200) | NULL | 户型逻辑 |
|
||
| `wall_thickness` | VARCHAR(100) | NULL | 墙体厚度 |
|
||
| `seismic_rating` | VARCHAR(100) | NULL | 抗震设防 |
|
||
| `insulation_type` | VARCHAR(100) | NULL | 保温形式 |
|
||
| `roof_structure` | VARCHAR(200) | NULL | 屋面构造 |
|
||
| — **设计理念** — | | | |
|
||
| `design_concept` | TEXT | NULL | 设计理念富文本 |
|
||
| `concept_style` | VARCHAR(100) | NULL | 风格提炼 |
|
||
| `concept_material` | VARCHAR(100) | NULL | 材质提炼 |
|
||
| `concept_target` | VARCHAR(100) | NULL | 受众提炼 |
|
||
| — **分类特有扩展** — | | | |
|
||
| `meta` | JSON | NULL | 分类特有参数（景观面积 / 客房数 / 主材等） |
|
||
| — **统计计数** — | | | |
|
||
| `view_count` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `like_count` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `favorite_count` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `download_count` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `comment_count` | INT UNSIGNED | DEFAULT 0 | |
|
||
| — **时间** — | | | |
|
||
| `published_at` | TIMESTAMP | NULL | 发布时间 |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
| `deleted_at` | TIMESTAMP | NULL | |
|
||
|
||
索引：`uk_cases_code`, `idx_cases_category_id`, `idx_cases_status`, `idx_cases_is_featured`, `idx_cases_status_created (status, created_at)`, `idx_cases_status_featured (status, is_featured, featured_sort)`
|
||
|
||
> `meta` JSON 用于存储不同分类的特有参数。例如：
|
||
> - 景观类案例：`{"landscape_area": 270, "main_vegetation": "黑松/苔藓", "paving_material": "花岗岩/白砂"}`
|
||
> - 民宿类案例：`{"room_count": 12, "room_note": "12间套房"}`
|
||
> - 室内类案例：`{"main_material": "微水泥/钢构"}`
|
||
> - 商业类案例：`{"building_height": "120m", "function_layout": "办公/商业"}`
|
||
|
||
#### `case_images` — 案例图库
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `case_id` | BIGINT UNSIGNED | FK → cases | |
|
||
| `type` | VARCHAR(20) | NOT NULL | effect / floor_plan / elevation |
|
||
| `url` | VARCHAR(500) | NOT NULL | 图片 URL |
|
||
| `thumbnail_url` | VARCHAR(500) | NULL | 缩略图 URL |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_case_images_case_type (case_id, type)`
|
||
|
||
#### `case_files` — 案例文件资产
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `case_id` | BIGINT UNSIGNED | FK → cases | |
|
||
| `stage` | VARCHAR(20) | NOT NULL | floor_plan / exterior / construction / structure / interior / courtyard |
|
||
| `file_name` | VARCHAR(200) | NOT NULL | 原始文件名 |
|
||
| `file_path` | VARCHAR(500) | NOT NULL | 存储路径 / OSS key |
|
||
| `file_size` | BIGINT UNSIGNED | NOT NULL | 字节数 |
|
||
| `file_ext` | VARCHAR(20) | NOT NULL | dwg / pdf / rvt / jpg / png / zip |
|
||
| `mime_type` | VARCHAR(100) | NULL | |
|
||
| `uploader_id` | BIGINT UNSIGNED | FK → users | 上传者 |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_case_files_case_stage (case_id, stage)`
|
||
|
||
> `stage` 枚举值对应 PRD F5.1 的 6 个设计阶段：
|
||
> - `floor_plan` — 平面阶段
|
||
> - `exterior` — 外观阶段
|
||
> - `construction` — 施工图阶段
|
||
> - `structure` — 结构阶段
|
||
> - `interior` — 室内阶段
|
||
> - `courtyard` — 庭院阶段
|
||
|
||
#### `case_belongs_tag` — 案例-标签关联
|
||
|
||
| 字段 | 类型 | 约束 |
|
||
|------|------|------|
|
||
| `case_id` | BIGINT UNSIGNED | PK(联合), FK → cases |
|
||
| `tag_id` | BIGINT UNSIGNED | PK(联合), FK → tags |
|
||
|
||
#### `case_team_members` — 案例主创团队
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `case_id` | BIGINT UNSIGNED | FK → cases | |
|
||
| `designer_id` | BIGINT UNSIGNED | FK → designers | |
|
||
| `role` | VARCHAR(30) | NOT NULL | scheme / structure / mep (方案/结构/水电) |
|
||
| `is_lead` | TINYINT | DEFAULT 0 | 是否主创 |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
|
||
索引：`uk_case_team_case_designer_role (case_id, designer_id, role)`, `idx_case_team_designer_id`
|
||
|
||
---
|
||
|
||
### 4.3 设计师域
|
||
|
||
#### `designers` — 设计师档案
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `name_cn` | VARCHAR(50) | NOT NULL | 中文姓名 |
|
||
| `name_en` | VARCHAR(100) | NULL | 英文姓名 |
|
||
| `title` | VARCHAR(50) | NULL | 职位：方案主创设计师 |
|
||
| `title_badge` | VARCHAR(50) | NULL | 职位徽章：Lead Architect |
|
||
| `company` | VARCHAR(100) | NULL | 所属机构 |
|
||
| `city` | VARCHAR(50) | NULL | 所在城市 |
|
||
| `portrait` | VARCHAR(500) | NULL | 个人写真 URL |
|
||
| `portrait_caption` | VARCHAR(100) | NULL | STUDIO PORTRAIT © 2023 |
|
||
| `years_of_experience` | TINYINT UNSIGNED | NULL | 从业年限 |
|
||
| `project_count` | INT UNSIGNED | DEFAULT 0 | 完成项目数 |
|
||
| `design_philosophy_quote` | TEXT | NULL | 设计理念引用语 |
|
||
| `design_philosophy` | TEXT | NULL | 设计理念详述（富文本） |
|
||
| `follower_count` | INT UNSIGNED | DEFAULT 0 | 关注者数量 |
|
||
| `view_count` | INT UNSIGNED | DEFAULT 0 | 主页浏览量 |
|
||
| `is_active` | TINYINT | DEFAULT 1 | 是否公开展示 |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
| `deleted_at` | TIMESTAMP | NULL | |
|
||
|
||
索引：`idx_designers_is_active`
|
||
|
||
#### `designer_awards` — 设计师荣誉奖项
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `designer_id` | BIGINT UNSIGNED | FK → designers | |
|
||
| `year` | SMALLINT UNSIGNED | NOT NULL | 获奖年份 |
|
||
| `title` | VARCHAR(200) | NOT NULL | 红点设计大奖 Red Dot Award |
|
||
| `description` | VARCHAR(300) | NULL | 最佳室内设计类 - The Cloud Villa |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_awards_designer_year (designer_id, year DESC)`
|
||
|
||
#### `designer_belongs_tag` — 设计师-专长标签
|
||
|
||
| 字段 | 类型 | 约束 |
|
||
|------|------|------|
|
||
| `designer_id` | BIGINT UNSIGNED | PK(联合), FK → designers |
|
||
| `tag_id` | BIGINT UNSIGNED | PK(联合), FK → tags (type=specialty) |
|
||
|
||
---
|
||
|
||
### 4.4 运营内容域
|
||
|
||
#### `banners` — 首页轮播
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `title` | VARCHAR(200) | NOT NULL | 大标题 |
|
||
| `description` | TEXT | NULL | 描述摘要 |
|
||
| `image` | VARCHAR(500) | NOT NULL | 背景大图 URL |
|
||
| `label` | VARCHAR(50) | NULL | 标签文字：精选项目 |
|
||
| `link_type` | VARCHAR(20) | NOT NULL, DEFAULT 'case' | case / topic / external |
|
||
| `link_url` | VARCHAR(500) | NOT NULL | 跳转链接 |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | |
|
||
| `is_active` | TINYINT | DEFAULT 1 | 启用/停用 |
|
||
| `published_at` | TIMESTAMP | NULL | 展示日期 |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_banners_active_sort (is_active, sort)`
|
||
|
||
#### `topics` — 专题合集
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `title` | VARCHAR(200) | NOT NULL | 专题标题 |
|
||
| `cover_image` | VARCHAR(500) | NULL | 封面图 |
|
||
| `description` | TEXT | NULL | 富文本描述 |
|
||
| `status` | VARCHAR(20) | NOT NULL, DEFAULT 'draft' | draft / published |
|
||
| `case_count` | INT UNSIGNED | DEFAULT 0 | 关联案例数 |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
| `deleted_at` | TIMESTAMP | NULL | |
|
||
|
||
#### `topic_belongs_case` — 专题-案例关联
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `topic_id` | BIGINT UNSIGNED | PK(联合), FK → topics | |
|
||
| `case_id` | BIGINT UNSIGNED | PK(联合), FK → cases | |
|
||
| `sort` | INT UNSIGNED | DEFAULT 0 | 专题内排序 |
|
||
|
||
---
|
||
|
||
### 4.5 用户互动域
|
||
|
||
#### `favorites` — 收藏
|
||
|
||
| 字段 | 类型 | 约束 |
|
||
|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users |
|
||
| `case_id` | BIGINT UNSIGNED | FK → cases |
|
||
| `created_at` | TIMESTAMP | |
|
||
|
||
索引：`uk_favorites_user_case (user_id, case_id)`, `idx_favorites_case_id`
|
||
|
||
#### `likes` — 点赞
|
||
|
||
| 字段 | 类型 | 约束 |
|
||
|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users |
|
||
| `case_id` | BIGINT UNSIGNED | FK → cases |
|
||
| `created_at` | TIMESTAMP | |
|
||
|
||
索引：`uk_likes_user_case (user_id, case_id)`, `idx_likes_case_id`
|
||
|
||
#### `comments` — 评论
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `case_id` | BIGINT UNSIGNED | FK → cases | |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users | |
|
||
| `parent_id` | BIGINT UNSIGNED | NULL, FK → comments | 回复嵌套 |
|
||
| `content` | TEXT | NOT NULL | 评论内容 |
|
||
| `status` | VARCHAR(20) | DEFAULT 'pending' | pending / approved / rejected |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
| `deleted_at` | TIMESTAMP | NULL | |
|
||
|
||
索引：`idx_comments_case_status (case_id, status)`, `idx_comments_user_id`, `idx_comments_parent_id`
|
||
|
||
#### `follows` — 关注设计师
|
||
|
||
| 字段 | 类型 | 约束 |
|
||
|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users |
|
||
| `designer_id` | BIGINT UNSIGNED | FK → designers |
|
||
| `created_at` | TIMESTAMP | |
|
||
|
||
索引：`uk_follows_user_designer (user_id, designer_id)`, `idx_follows_designer_id`
|
||
|
||
#### `newsletter_subscriptions` — 邮箱订阅
|
||
|
||
| 字段 | 类型 | 约束 |
|
||
|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK |
|
||
| `email` | VARCHAR(200) | NOT NULL, UNIQUE |
|
||
| `created_at` | TIMESTAMP | |
|
||
|
||
---
|
||
|
||
### 4.6 日志与系统域
|
||
|
||
#### `user_login_logs` — 登录日志
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users | |
|
||
| `login_type` | VARCHAR(20) | NOT NULL | password / sms / qr / wechat / qq / enterprise_sso |
|
||
| `ip` | VARCHAR(45) | NULL | |
|
||
| `login_location` | VARCHAR(100) | NULL | 登录地理位置（由 IP 库反查填充） |
|
||
| `device_fingerprint` | VARCHAR(128) | NULL | 设备指纹 |
|
||
| `risk_level` | VARCHAR(10) | NULL | L1 / L2 / L3 / L4 |
|
||
| `risk_reason` | VARCHAR(255) | NULL | 命中策略摘要 |
|
||
| `result` | VARCHAR(20) | NOT NULL, DEFAULT 'success' | success / failed / blocked |
|
||
| `user_agent` | VARCHAR(500) | NULL | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_login_logs_user_created (user_id, created_at)`, `idx_login_logs_ip_created (ip, created_at)`
|
||
|
||
#### `user_sessions` — 活跃会话
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users | |
|
||
| `session_token` | VARCHAR(255) | NOT NULL, UNIQUE | Refresh Token 标识（哈希值） |
|
||
| `device_name` | VARCHAR(100) | NULL | 设备名称（如 Chrome on macOS） |
|
||
| `device_fingerprint` | VARCHAR(128) | NULL | 设备指纹 |
|
||
| `ip` | VARCHAR(45) | NULL | 登录 IP |
|
||
| `login_location` | VARCHAR(100) | NULL | 登录地理位置 |
|
||
| `is_current` | TINYINT | NOT NULL, DEFAULT 0 | 是否为当前请求会话 |
|
||
| `last_active_at` | TIMESTAMP | NULL | 最近活跃时间 |
|
||
| `expires_at` | TIMESTAMP | NOT NULL | 会话过期时间 |
|
||
| `revoked_at` | TIMESTAMP | NULL | 被踢出/注销时间（非空即失效） |
|
||
| `created_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_sessions_user_active (user_id, revoked_at, expires_at)`, `uk_sessions_token (session_token)`
|
||
|
||
> 活跃会话表用于 F1.9 多端会话管理。创建会话时写入，Token 刷新时更新 `last_active_at`，退出/踢出时设置 `revoked_at`。定期清理过期记录。
|
||
|
||
#### `security_risk_events` — 风控事件日志
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `user_id` | BIGINT UNSIGNED | NULL, FK → users | 可为空（未登录攻击流量） |
|
||
| `event_type` | VARCHAR(30) | NOT NULL | login / sms_send / register / reset_password / sso_callback |
|
||
| `risk_level` | VARCHAR(10) | NOT NULL | L1 / L2 / L3 / L4 |
|
||
| `action` | VARCHAR(30) | NOT NULL | captcha_upgrade / rate_limit / temporary_block / blacklist |
|
||
| `ip` | VARCHAR(45) | NULL | |
|
||
| `device_fingerprint` | VARCHAR(128) | NULL | |
|
||
| `user_agent` | VARCHAR(500) | NULL | |
|
||
| `payload` | JSON | NULL | 事件上下文 |
|
||
| `created_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_risk_events_user_created (user_id, created_at)`, `idx_risk_events_level_created (risk_level, created_at)`, `idx_risk_events_ip_created (ip, created_at)`
|
||
|
||
> 风控事件日志不可编辑、不可删除，无 `updated_at` 和 `deleted_at`。
|
||
|
||
#### `security_blacklists` — 安全黑名单
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `target_type` | VARCHAR(20) | NOT NULL | ip / device / account |
|
||
| `target_value` | VARCHAR(255) | NOT NULL | IP、设备指纹或账号标识 |
|
||
| `reason` | VARCHAR(255) | NULL | 拉黑原因 |
|
||
| `source` | VARCHAR(20) | NOT NULL, DEFAULT 'system' | system / manual |
|
||
| `status` | VARCHAR(20) | NOT NULL, DEFAULT 'active' | active / released / expired |
|
||
| `expire_at` | TIMESTAMP | NULL | 为空表示永久 |
|
||
| `created_by` | BIGINT UNSIGNED | NULL, FK → users | 手动操作人（系统自动为空） |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
索引：`uk_blacklists_target (target_type, target_value)`, `idx_blacklists_status_expire (status, expire_at)`
|
||
|
||
#### `admin_operation_logs` — 管理端操作日志
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users | 操作人 |
|
||
| `action` | VARCHAR(30) | NOT NULL | create / update / delete / status_change |
|
||
| `resource_type` | VARCHAR(50) | NOT NULL | case / banner / topic / designer / comment |
|
||
| `resource_id` | BIGINT UNSIGNED | NOT NULL | 操作对象 ID |
|
||
| `before_value` | JSON | NULL | 变更前值 |
|
||
| `after_value` | JSON | NULL | 变更后值 |
|
||
| `ip` | VARCHAR(45) | NULL | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_op_logs_user_created (user_id, created_at)`, `idx_op_logs_resource (resource_type, resource_id)`
|
||
|
||
> 操作日志不可编辑、不可删除，无 `updated_at` 和 `deleted_at`。
|
||
|
||
#### `download_logs` — 文件下载日志
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `user_id` | BIGINT UNSIGNED | FK → users | |
|
||
| `case_id` | BIGINT UNSIGNED | FK → cases | |
|
||
| `case_file_id` | BIGINT UNSIGNED | FK → case_files | |
|
||
| `ip` | VARCHAR(45) | NULL | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
|
||
索引：`idx_dl_logs_user_created (user_id, created_at)`, `idx_dl_logs_case_id`
|
||
|
||
#### `system_configs` — 系统配置
|
||
|
||
| 字段 | 类型 | 约束 | 说明 |
|
||
|------|------|------|------|
|
||
| `id` | BIGINT UNSIGNED | PK | |
|
||
| `group` | VARCHAR(50) | NOT NULL | site / seo / upload / comment / watermark / security |
|
||
| `key` | VARCHAR(100) | NOT NULL | |
|
||
| `value` | TEXT | NULL | |
|
||
| `created_at` | TIMESTAMP | | |
|
||
| `updated_at` | TIMESTAMP | | |
|
||
|
||
索引：`uk_configs_group_key (group, key)`
|
||
|
||
---
|
||
|
||
## 5. 索引策略
|
||
|
||
### 必须索引
|
||
- 所有外键字段
|
||
- `status` 字段
|
||
- `created_at` 字段
|
||
- 唯一业务标识（`cases.code`、`tags.type+name`、`users.phone`）
|
||
|
||
### 核心复合索引
|
||
```sql
|
||
-- 案例列表查询（分类 + 状态 + 时间）
|
||
idx_cases_category_status_created (category_id, status, created_at)
|
||
|
||
-- 案例精选排序
|
||
idx_cases_featured_sort (status, is_featured, featured_sort)
|
||
|
||
-- 文件按阶段查询
|
||
idx_case_files_case_stage (case_id, stage)
|
||
|
||
-- 评论列表
|
||
idx_comments_case_status (case_id, status, created_at)
|
||
```
|
||
|
||
### 索引限制
|
||
- 单表索引不超过 6 个
|
||
- 避免冗余索引
|
||
- 定期检查未使用的索引
|
||
|
||
---
|
||
|
||
## 6. 百万级数据表设计
|
||
|
||
### 分页优化
|
||
```sql
|
||
-- 游标分页（案例列表推荐使用）
|
||
SELECT * FROM cases WHERE status = 'published' AND id > :last_id ORDER BY id DESC LIMIT 20;
|
||
```
|
||
|
||
### 大表 COUNT 优化
|
||
```sql
|
||
-- 使用 categories.case_count / tags.reference_count 缓存计数
|
||
-- 使用 cases.view_count / like_count / favorite_count 避免 COUNT JOIN
|
||
```
|
||
|
||
### 分区策略（日志表）
|
||
```sql
|
||
-- 管理操作日志
|
||
ALTER TABLE admin_operation_logs PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
|
||
PARTITION p202601 VALUES LESS THAN (202602),
|
||
PARTITION p202602 VALUES LESS THAN (202603),
|
||
PARTITION pmax VALUES LESS THAN MAXVALUE
|
||
);
|
||
|
||
-- 用户登录日志（数据量最大，优先分区）
|
||
ALTER TABLE user_login_logs PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
|
||
PARTITION p202601 VALUES LESS THAN (202602),
|
||
PARTITION p202602 VALUES LESS THAN (202603),
|
||
PARTITION pmax VALUES LESS THAN MAXVALUE
|
||
);
|
||
|
||
-- 风控事件日志
|
||
ALTER TABLE security_risk_events PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
|
||
PARTITION p202601 VALUES LESS THAN (202602),
|
||
PARTITION p202602 VALUES LESS THAN (202603),
|
||
PARTITION pmax VALUES LESS THAN MAXVALUE
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
*最后更新: 2026-02-28*
|