mine/hexo-blog @ main

title: 经典表设计思考 author: Gamehu date: 2025-12-02 16:36:56 tags:

详细设计 categories:
工作 ---

写在前面

我评审了团队里几位研发程师提交的《xx详细设计》。

在评审数据库设计（Schema Design）部分时，我们针对“资产表的独立性”、“复杂 JSON 的存储策略”以及“列表查询性能”展开了几轮讨论。起初他的设计偏向于“开发省事”，而我更强调“业务边界”和“长期可维护性”。这样的场景在我职业生涯中其实遇到好多次，虽然每次可能都稍有不同但是我个人认为核心考量规则是不变的。

这次讨论非常有代表性，不仅解决了一个具体的业务场景，更折射出 SaaS 系统设计中通用的取舍逻辑。复盘如下，与大家分享。

摘要：在现代 SaaS 架构中，越来越多的业务场景需要依赖“外部异步工作流”来生成核心数据（例如：调用一个耗时的分析流、审批流或数据处理管道）。本文复盘了一个垂直领域 SaaS 系统的数据库演进过程，探讨在异步任务与复杂结构化数据的双重挑战下，如何进行合理的数据库建模。

一、业务场景与技术挑战

我们正在开发一套垂直领域的 SaaS 系统，核心业务链路是：客户 (Customer) -> 资产 (Asset, 如宠物/设备) -> 业务记录 (Record)。

核心痛点：系统的核心记录（Record）不是由用户简单的 CRUD 生成的，而是依赖一个外部异步工作流 (External Workflow)。

触发：用户上传基础素材（如音频/文件）。
处理：系统调用外部 Workflow 引擎进行处理（耗时不定）。
结果：Workflow 回调返回一个包含多板块、多维度的复杂 JSON 数据。

架构师面临的三个核心问题：

资产定义的独立性：当核心记录依赖工作流生成时，资产信息（如宠物/设备基础信息）应该包含在记录里，还是独立建表？
结构化与灵活性的平衡：工作流返回的是一个大 JSON，数据库设计是该“打散成列”还是“整存整取”？
历史数据的不可变性：资产状态会随时间改变（如改名、升级），如何保证历史记录的准确性，同时兼顾列表查询性能？

二、架构演进：从“耦合”到“解耦”

1. 资产的独立性：解耦业务边界

最初的想法： “既然每次调用工作流都是针对某个对象的，能不能直接把对象信息存在记录表里？减少表关联。”

架构决策：坚决剥离资产表（如 t_pet），建立星型拓扑。

战略考量：
- 业务解耦：资产是核心实体，它可能会被未来的其他业务模块（如电商、预约、CRM）复用。如果强绑定在当前这个“工作流记录表”中，新业务将无法复用该资产数据。
- 生命周期分离：资产的生命周期（长期、可变）与工作流记录的生命周期（一次性、不可变）完全不同，必须物理分离。

2. Workflow 数据的落地：混合存储策略

面临挑战：工作流返回的数据极其复杂，既包含核心指标（如数值、状态），也包含大量的描述性文本和嵌套结构。

架构决策：“核心指标列式存储” + “业务载荷 JSONB 存储”。

计算层 (SQL)：将工作流返回结果中，后续需要参与计算、统计、全局搜索的字段（如 weight, status, result_summary），提取为独立的数据库列。
展示层 (NoSQL)：将工作流返回的完整业务载荷（Payload），直接以 JSONB 格式存入 form_data 字段。这既应对了工作流输出结构的潜在变化，又简化了前端渲染逻辑。

3. 历史快照与性能优化：反范式化设计

面临挑战：列表页需要展示资产当时的名称、客户的联系方式。如果采用完全范式化设计（Join Asset Join Customer），不仅查询性能受限，且一旦资产发生变更（如过户），历史记录的展示就会失真。

架构决策：在记录表中引入“快照冗余” (Snapshot Redundancy)。

在工作流完成并写入数据库的那一刻，将当时的 关键资产属性（如名称、归属人）冗余写入记录表。

性能收益：列表查询实现 0 JOIN，单表极速返回。
业务收益：保留了“业务现场”，记录了工作流执行时的真实状态，不受后续资产变更影响。

4. 存储容量评估：单表足矣

面临挑战：是否需要将记录表拆分为 Record_Master 和 Record_Detail？

架构决策：回归“单表设计”。

经过评估，外部工作流返回的结果主要是结构化数据，不包含巨大的二进制文件（BLOB）或超长文本（Log）。单行数据量控制在合理范围内（< 2KB）。在 PostgreSQL 等现代数据库中，单表完全足以支撑百万级业务数据，拆分反而增加了事务复杂性。

三、最终架构方案 (PostgreSQL)

1. 领域模型设计 (ER Diagram)

erDiagram
    %% 核心资产：独立存在，作为公共底座
    t_asset {
        bigint id PK
        varchar name
        jsonb current_properties "当前属性(可变)"
    }

    %% 业务记录：承载工作流结果
    t_asset ||--|{ t_workflow_record : "1:N"
    t_workflow_record {
        bigint id PK
        
        %% 冗余快照 (Snapshot)
        varchar snapshot_asset_name "当时名称"
        varchar snapshot_owner_info "当时归属人"
        
        %% 核心指标 (Structured Columns)
        decimal key_metric_a "用于计算"
        varchar key_status_b "用于统计"
        
        %% 柔性载荷 (Flexible Payload)
        jsonb workflow_result "完整结果数据"
        
        %% 流程状态
        varchar process_status "INIT, PROCESSING, DONE"
    }

2. 生产级 SQL DDL

-- 1. 资产表：核心实体，支撑多业务线
CREATE TABLE t_pet (
    id BIGSERIAL PRIMARY KEY,
    owner_id BIGINT NOT NULL, 
    name VARCHAR(64) NOT NULL,
    current_weight DECIMAL(5,2), -- 资产当前状态
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. 记录表：存储工作流结果
CREATE TABLE t_medical_record (
    id BIGSERIAL PRIMARY KEY,
    pet_id BIGINT NOT NULL REFERENCES t_pet(id),
    
    -- 【快照区】冗余字段，确保列表页 0 JOIN，且保留历史原貌
    snapshot_owner_phone VARCHAR(20),
    snapshot_pet_name VARCHAR(64),
    
    -- 【核心指标区】独立列，用于后续的报表统计与逻辑计算
    diagnosis_code VARCHAR(50),  -- 诊断编码
    recorded_weight DECIMAL(5, 2), -- 当时记录的数值
    
    -- 【柔性载荷区】存储工作流返回的完整复杂结构
    -- 利用 JSONB 存储，适应 Schema 的动态变化
    form_data JSONB DEFAULT '{}',
    
    process_status VARCHAR(32) DEFAULT 'INIT', -- 状态机
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 索引优化：针对快照字段建立索引，加速搜索
CREATE INDEX idx_record_search ON t_medical_record(snapshot_owner_phone varchar_pattern_ops);

四、方法论总结：五维数据建模法

针对“SaaS + 复杂外部工作流”场景，我们总结了以下决策模版：

1. 业务边界维 (Extensibility)

决策点：该对象未来是否会被其他业务模块复用？
法则：如果可能，必须独立建表，下沉为公共数据底座。

2. 时间属性维 (Asset vs Event)

决策点：数据是“会变化的状态”还是“不可变的历史”？
法则：资产（Asset）独立存最新状态；记录（Record）存工作流执行时的历史切片。

3. 数据使用维 (Column vs JSON)

决策点：字段是否用于 SQL 筛选、聚合统计或后端计算？
法则：要算、要搜的做成列；只用于展示、结构易变的扔进 JSON。

4. 查询性能维 (Normalization vs Snapshot)

决策点：列表页是否高频？业务是否需要追溯“发生当时”的状态？
法则：在记录表中冗余关键搜索字段和历史状态快照。

5. 存储容量维 (Split vs Merge)

决策点：单行数据量是否显著过大（如含原始长文本/大图）？
法则：存原始大素材 -> 拆主子表；只存处理后的结构化数据 -> 单表搞定。

结语

无论是对接 AI、IoT 设备还是审批流，架构设计的本质是不变的：**厘清资产与事件的边界，在结构化与灵活性之间找到平衡。**这套设计模式能有效降低系统的复杂性，并为未来的业务扩展预留充足的空间。

经典表设计思考.md 8.7 KB 永久連結 文件歷史 原始文件