数仓建模：一种动态字段表模型设计方法与应用

0 引言

在当今数据驱动的业务环境中，数据的结构和需求往往是多变的。尤其是在一些新兴的业务场景或快速迭代的项目中，传统的固定字段表结构可能无法满足灵活性和扩展性的要求。此时，动态字段表模型的设计就显得尤为重要。以下将详细介绍一种基于实际项目经验的动态字段表模型设计方法。

1 问题背景

电商公司搭建一个商品管理系统，该电商公司销售的商品种类繁多，涵盖了电子产品、服装、家居用品、食品等多个品类。每个品类的商品都有其独特的属性，例如电子产品的屏幕尺寸、内存大小、处理器型号；服装的颜色、尺码、材质；家居用品的尺寸、材质、风格等。而且，随着市场的变化和业务的拓展，商品的属性可能会不断增加或变化，这就要求我们设计的表模型能够灵活应对这些动态变化。

2 需求分析

核心需求如下：

• 能够支持不同品类商品的多样化属性存储，且属性可随时添加、修改或删除，而无需对数据库表结构进行频繁的修改操作。

• 方便查询和统计各类商品的属性信息，以满足业务分析和运营决策的需求，例如查询某类商品中特定属性的分布情况，或者统计具有特定属性组合的商品数量。

• 确保数据的完整性和一致性，在动态字段的管理过程中，避免出现数据丢失、重复或不一致的情况。

3.动态表模型设计方案

3.1 预留字段法

• 基本思路

  在表结构设计时，预先预留出一定数量的备用字段，这些字段在初始阶段可能不使用，但当有新的动态数据需要添加时，可以直接利用这些预留字段，而无需修改表结构。

• 示例

  创建一个 “员工信息表”，除了常规的 “姓名”“年龄”“性别”“部门” 等字段外，预留 5 个备用字段 “extra1”“extra2”“extra3”“extra4”“extra5”。当需要记录员工的额外信息，如特殊技能、证书获取情况等，可将相关信息依次存入这些备用字段中。

• 优点

  简单直观，对于少量且固定的动态字段需求，实施起来较为便捷，不需要复杂的技术架构，可快速满足基本的业务扩展需求，开发人员容易理解和操作，并且对数据库性能的影响相对较小，因为不需要进行复杂的关联查询和数据解析操作。

• 缺点

  缺乏灵活性和扩展性，预留字段数量难以确定，过多会造成存储空间浪费，过少则无法满足后续未知的动态字段扩展，而且如果预留字段的数据类型不一致，可能会导致数据存储和查询的混乱，不利于数据的管理和维护，也无法很好地应对复杂的动态数据结构变化。

3.2 键值对存储法

• 基本思路

  将动态字段以键值对的形式存储在一个单独的表中，该表与主表通过一个关联字段进行关联，从而实现动态字段的灵活存储和扩展。

• 示例

  在一个电商系统中，对于商品的扩展属性，如颜色、尺寸、材质等，创建一个 “商品扩展属性表”，包含 “商品_id”“属性_key”“属性_value” 三个字段。“商品_id” 与 “商品表” 中的 “id” 字段关联，“属性_key” 用于存储属性名称，如 “颜色”“尺寸” 等，“属性_value” 用于存储对应属性的具体值，如 “红色”“XL” 等。

• 优点

  高度灵活，能够适应各种类型的动态字段，新的字段可以随时添加而无需更改表结构，适用于字段数量和名称不确定的情况，方便存储和查询复杂的结构化数据，数据的扩展性强，对于一些需要频繁修改和扩展动态字段的业务场景非常适用。

• 缺点

  查询性能相对较低，由于需要通过关联字段进行键值对的匹配查询，当数据量较大时，可能会导致查询效率低下，而且数据的完整性和一致性维护相对复杂，因为键值对的格式较为松散，容易出现数据录入错误或不一致的情况，对开发人员的数据处理能力要求较高。

3.3 实体 - 属性 - 值模型（EAV）

• 基本思路

  该模型将实体、属性和值分别存储在不同的表中，通过建立关联关系来表示实体的各种属性及其对应的值，能够很好地处理动态变化的属性。

• 示例

  在一个医疗信息管理系统中，对于患者的各种检查结果和病历信息，创建 “患者表”“检查项目表”“患者检查结果表”。“患者表” 存储患者的基本信息；“检查项目表” 存储所有可能的检查项目名称；“患者检查结果表” 通过 “患者_id” 和 “检查项目_id” 与前两个表关联，存储具体患者的具体检查项目的结果值，这样可以方便地添加新的检查项目和记录不同患者的各种检查结果。

• 优点

  具有很强的通用性和扩展性，能够处理各种复杂的动态数据结构，无论是实体的属性数量还是属性类型的变化，都可以轻松应对，方便对数据进行灵活的查询和统计分析，尤其适合于需要对数据进行多角度、多层次分析的业务场景，例如医疗、科研等领域的数据管理。

• 缺点

  表结构复杂，涉及多个关联表，导致查询语句编写困难，性能开销较大，尤其是在处理大规模数据时，连接操作会消耗大量的数据库资源，而且数据的理解和维护成本高，对于不熟悉 EAV 模型的开发人员和业务人员来说，理解和操作数据较为困难。

3.4 JSON 或 XML 字段类型法

• 基本思路

  利用数据库支持的 JSON 或 XML 数据类型，将动态字段以 JSON 或 XML 格式的字符串存储在一个字段中，通过解析该字符串来获取和操作动态字段的值。

• 示例

  在一个内容管理系统中，对于文章的元数据，如标签、分类、作者简介等，创建一个 “文章表”，其中有一个 “元数据” 字段，其类型为 JSON。当需要添加新的元数据时，只需在 JSON 字符串中添加相应的键值对即可，如 {"tags":["科技","人工智能"],"category":"技术文章","author_intro":"资深科技记者"}。

3.5 版本控制法

• 基本思路

  为表添加一个版本字段，每次表结构发生变化时，更新版本号，并根据需要创建新的表结构或在原表中添加新的字段，同时通过版本号来区分不同时期的数据记录和处理方式。

• 示例

  在一个软件项目管理系统中，随着项目的进展，对于项目任务的属性需求可能会不断增加。创建 “项目任务表” 并添加 “版本” 字段，初始版本时任务表包含 “任务名称”“负责人”“截止日期” 等基本字段，当需要添加新的字段如 “任务优先级”“任务难度系数” 等时，更新版本号，并在表中添加新字段，同时根据版本号对不同时期创建的任务进行不同的处理和展示。

• 优点

• • 结构清晰有序

    版本控制法通过明确的版本号来区分不同时期的表结构，使得数据的演变过程一目了然。每次表结构发生变化时，更新版本号并记录变更内容，这有助于开发人员和维护人员理解数据的历史状态和变化轨迹，方便进行数据追溯和审计。

• • 有效管理变更

    ：可以根据业务需求逐步更新表结构，添加新的字段以适应动态变化。在不同版本下，数据的存储和处理逻辑相对独立，降低了因表结构频繁变动而引发的数据混乱风险。例如，当业务规则发生重大变化，需要对数据库进行大规模改造时，通过版本控制可以有条不紊地进行过渡，而不是一次性对所有数据进行复杂的处理。

• • 兼容性较好

    ：对于一些需要与外部系统或旧有程序交互的场景，版本控制法可以提供一定程度的兼容性。旧版本的程序可以继续访问和处理对应版本的数据，而新版本的程序则能够利用新的字段和功能，避免了对整个系统的全面升级要求，减少了系统更新的阻力。

    缺点

• • 数据冗余和复杂性增加

    ：随着版本的更新，不同版本的表结构可能会导致数据在一定程度上的冗余。例如，旧版本的字段可能在新版本中仍然保留，只是添加了新的字段，这会占用更多的存储空间。同时，维护多个版本的表结构也增加了数据库的复杂性，需要更多的管理工作来确保各个版本之间的协调和数据一致性。

• • 查询和维护成本上升

    ：在进行数据查询和统计时，需要考虑版本因素。如果要获取跨版本的数据或者进行综合分析，可能需要编写复杂的查询语句来关联不同版本的表，这增加了查询的难度和成本。而且，在对数据库进行维护操作（如备份、恢复、索引优化等）时，需要对每个版本的表分别进行处理，增加了维护工作量。

• • 版本迁移困难

    ：当从一个版本迁移到另一个版本时，尤其是涉及到数据结构的重大变化（如字段的数据类型变更、字段的删除等），需要编写复杂的迁移脚本，以确保数据的完整性和正确性。这个过程容易出错，并且如果迁移过程中出现问题，可能会导致数据丢失或不一致的情况。

3.5 不同方法适用场景总结

• 没有一种绝对最佳的动态字段建表方法，而是需要根据具体的业务场景、数据规模、性能要求、开发团队的技术能力等因素来综合考虑和权衡：

• 如果业务场景相对简单，动态字段数量较少且固定，对性能要求较高，预留字段法可能是一个简单有效的选择。

• 对于需要频繁添加和修改动态字段，且对查询性能要求不是特别苛刻的场景，键值对存储法或 JSON/XML 字段类型法可以提供较好的灵活性和扩展性。

• 而在处理复杂的结构化数据，需要进行深入的数据分析和挖掘，且开发团队具备较强的技术能力和数据库管理经验的情况下，实体 - 属性 - 值模型（EAV）可能更适合，但需要注意性能优化和数据维护的复杂性。

• 版本控制法在应对动态字段和表结构变化方面有其独特的优势，尤其适用于对数据演变过程要求严格、业务规则复杂且需要逐步更新的场景。但它也带来了数据冗余、查询复杂和版本迁移困难等问题。

4.本文问题解决方案

为了满足多样化的存储及上层灵活查询、分析需求，同时兼顾性能问题，本文采用EAV模型进行设计

（一）基础表设计

1. 商品主表（products）

 包含通用的商品信息字段，如商品 ID（product_id）、商品名称（product_name）、商品类别（category_id）、商品价格（price）、 库存数量（stock_quantity）等。这些字段是固定不变的，用于标识和描述商品的基本信息。商品 ID 作为主键，确保每条商品记录的唯一性。

2. 商品类别表（categories）

存储商品的类别信息，包括类别 ID（category_id）、类别名称（category_name）等字段。类别 ID 作为主键，与商品主表中的商品类别字段建立外键关联，用于实现商品与类别的关联关系，方便按照类别对商品进行管理和查询。

（二）动态字段存储表设计

1. 商品属性表（product_attributes）

用于存储商品的动态属性信息，包括属性 ID（attribute_id）、属性名称（attribute_name）、数据类型（data_type）等字段。属性 ID 作为主键，确保每个属性的唯一性。数据类型字段用于指定该属性所存储的数据类型，如字符串、整数、小数、日期等，以便在数据存储和查询时进行正确的处理。

2.商品属性值表（product_attribute_values）

该表是存储商品具体属性值的核心表，包含商品 ID（product_id）、属性 ID（attribute_id）、属性值（attribute_value）等字段。通过商品 ID 和属性 ID 与商品主表和商品属性表建立外键关联，形成一个完整的关联关系，从而能够准确地记录每个商品的各个动态属性值。

（三）实现过程

1.数据库创建与表结构搭建

使用关系型数据库管理系统（如 MySQL），按照上述设计方案创建相应的数据库和表结构。在创建表时，根据字段的特点和数据类型要求，合理设置字段的长度、约束条件等，例如将商品 ID 和类别 ID 设置为整数类型，并设置为主键，确保数据的唯一性和完整性。

2. 数据插入与更新

当添加新商品时，首先在商品主表中插入商品的基本信息，然后根据该商品所属的类别和具体的属性值，在商品属性值表中插入相应的记录。例如，对于一款新的智能手机，在商品主表中插入其名称、价格、库存等信息后，再在商品属性值表中插入其屏幕尺寸、内存容量、摄像头像素等属性值对应的记录。当商品的属性发生变化时，如新增某个属性或者修改某个属性的值，只需在商品属性表和商品属性值表中进行相应的插入或更新操作即可，而无需修改商品主表的结构。

3.数据查询与统计

 为了满足业务分析和查询的需求，编写了一系列的 SQL 查询语句。例如，要查询某一类商品的所有属性信息，可以通过关联商品主表、商品类别表、商品属性表和商品属性值表，使用合适的连接条件和筛选条件来获取所需的数据。具体的 SQL 查询语句如下：

SELECT p.product_name, c.category_name, a.attribute_name, av.attribute_valueFROM products pJOIN categories c ON p.category_id = c.category_idJOIN product_attribute_values av ON p.product_id = av.product_idJOIN product_attributes a ON av.attribute_id = a.attribute_idWHERE c.category_name = '电子产品'ORDER BY p.product_name, a.attribute_name;

若要统计具有特定属性组合的商品数量，例如统计屏幕尺寸为 6.5 英寸且内存容量为 8GB 的智能手机数量，可以使用如下的 SQL 查询语句：

SELECT COUNT(*)FROM products pJOIN product_attribute_values av1 ON p.product_id = av1.product_idJOIN product_attribute_values av2 ON p.product_id = av2.product_idJOIN product_attributes a1 ON av1.attribute_id = a1.attribute_idJOIN product_attributes a2 ON av2.attribute_id = a2.attribute_idWHERE a1.attribute_name = '屏幕尺寸' AND av1.attribute_value = '6.5英寸'AND a2.attribute_name = '内存容量' AND av2.attribute_value = '8GB'AND p.category_id = (SELECT category_id FROM categories WHERE category_name = '电子产品');

5.优势与缺点

优势

（1）灵活性高

能够轻松应对商品属性的动态变化，无论是新增属性还是修改已有属性，都可以方便地进行操作，而不会对整个系统的稳定性和扩展性造成太大影响。

（2）可扩展性强

随着业务的发展和新的商品品类的加入，只需在商品属性表中添加相应的属性记录，即可实现对新属性的支持，无需对数据库表结构进行大规模的重构。

（3）数据一致性好

通过合理的表结构设计和外键约束，确保了商品基本信息、类别信息、属性信息和属性值信息之间的一致性和完整性，避免了数据的混乱和错误。

缺点

（1）查询复杂度增加

由于数据存储在多个关联表中，一些复杂的查询可能需要编写较为复杂的 SQL 语句，涉及多个表的连接和筛选条件的设置，这对开发人员的 SQL 技能和数据库知识提出了较高的要求。

（2）性能优化难度较大

在处理大量数据和频繁的查询操作时，需要对数据库进行合理的索引优化和查询优化，以提高系统的性能和响应速度。否则，可能会出现查询效率低下、数据加载缓慢等问题。

6. 小结

通过本次项目的实践，我们成功地设计并实现了一种基于动态字段的表模型，有效地解决了电商公司商品管理系统中商品属性动态变化的问题。在实际应用中，该表模型表现出了较高的灵活性和可扩展性，能够满足业务不断发展的需求。然而，我们也意识到在使用动态字段表模型时所面临的挑战，如查询复杂度和性能优化等问题。在未来的项目中，我们将继续探索和研究更加高效、便捷的动态字段表模型设计方法和技术，结合数据库的新特性和优化策略，进一步提高系统的性能和稳定性，为企业的数据管理和业务发展提供更强大的支持。

7 案例拓展

工业生产中制程参数是动态的且参数较多，如何合理的设计表模型？ 

• 参数定义表（Parameter Definition Table）

• 目的

  用于存储制程参数的基本定义信息，包括参数名称、参数类型、单位等，这些信息相对稳定，不会随着具体生产批次而频繁变化。

• 结构示例

  Column Name	Data Type	Description
  parameter_id	Integer	制程参数的唯一标识符
  parameter_name	VARCHAR	制程参数的名称（如温度、压力等）
  parameter_type	VARCHAR	参数的数据类型（如数值型、字符型等）
  unit	VARCHAR	参数的单位（如摄氏度、帕斯卡等）

• 用途说明

  当有新的制程参数加入时，只需在这个表中添加新的记录，为后续关联其他表提供基础信息。例如，如果新增一个 “湿度” 制程参数，就可以插入一条记录（如parameter_id = 3，parameter_name = 'humidity'，parameter_type = 'numeric'，unit = '%'）。

参数标准表（Parameter Standards Table）

• 目的

  存储每个产品对于不同制程参数的合格标准，这是一个动态变化的部分，因为产品的制程标准可能会根据质量要求、工艺改进等因素而改变。

• 结构示例

  Column Name	Data Type	Description
  product_id	Integer	产品的唯一标识符
  parameter_id	Integer	制程参数的唯一标识符（关联参数定义表）
  lower_limit	DECIMAL（或其他合适类型）	制程参数合格范围的下限
  upper_limit	DECIMAL（或其他合适类型）	制程参数合格范围的上限

• 用途说明

  通过product_id和parameter_id的组合，可以灵活地为每个产品定义不同制程参数的合格标准。当制程标准发生变化时，只需更新这个表中的相应记录。例如，如果产品 1 的温度合格范围从95 - 105调整为90 - 100，就可以在这个表中更新对应的记录。

  这种设计方便了对不同产品和不同制程参数标准的管理，同时可以通过关联parameter_id与参数定义表，获取完整的参数信息。

生产数据表（Production Data Table）

• 目的

  记录实际生产过程中的详细数据，包括生产批次信息、产品信息、生产数量、次品数量以及实际的制程参数值。

• 结构示例（包含部分字段）

  Column Name	Data Type	Description
  production_id	Integer	生产批次的唯一标识符
  product_id	Integer	产品的唯一标识符（关联产品表）
  production_date	DATE	生产日期
  quantity_produced	Integer	生产数量
  quantity_defective	Integer	次品数量
  parameter_value_1	DECIMAL（或其他合适类型）	第一个制程参数的实际值（关联参数定义表）
  parameter_value_2	DECIMAL（或其他合适类型）	第二个制程参数的实际值（关联参数定义表）
  ...	...	...

• 用途说明

  • 生产数据表存储了实际生产中的动态数据，其中制程参数的值可以根据生产过程中的实际测量而记录。在设计时，可以根据实际的制程参数数量预留足够的字段（如parameter_value_1、parameter_value_2等），并通过程序逻辑或者数据库存储过程来确保这些值的正确插入和更新。

  • 与产品表和参数定义表的关联可以方便地获取产品的其他信息以及制程参数的详细定义，为后续计算良品率等操作提供完整的数据支持。

产品表（Product Table）（可选但推荐）

• 目的

  存储产品的基本信息，如产品名称、产品型号等。这有助于更好地组织和管理生产数据，特别是当涉及多种产品的生产时。

• 结构示例

  Column Name	Data Type	Description
  product_id	Integer	产品的唯一标识符（关联生产数据表）
  product_name	VARCHAR	产品的名称
  product_model	VARCHAR	产品的型号

• 用途说明

  • 通过与生产数据表的关联，可以在查询和分析生产数据时获取产品的详细名称和型号等信息，使数据更具可读性和可分析性。例如，在计算良品率并展示结果时，可以同时显示产品名称和型号，方便用户理解数据。

关联关系和数据完整性约束

• 关联关系

  • 生产数据表通过product_id与产品表关联，获取产品的基本信息。

  • 生产数据表中的制程参数值字段（如parameter_value_1等）通过参数定义表中的parameter_id间接关联，以确定每个参数值对应的参数名称、类型和单位等信息。

  • 参数标准表通过product_id与生产数据表关联，通过parameter_id与参数定义表关联，从而建立起产品、制程参数标准和实际生产数据之间的完整关系。

• 数据完整性约束

  • 在参数标准表中，product_id和parameter_id的组合应该是唯一的，以确保每个产品对于每个制程参数只有一组合格标准。

  • 在生产数据表中，production_id应该是唯一的，用于唯一标识每个生产批次。同时，可以设置外键约束，确保product_id在产品表中存在，制程参数值字段与参数定义表中的数据类型和其他约束相匹配。

这种表模型设计可以灵活地适应制程参数的动态变化，方便对生产数据和制程标准进行管理、查询和分析，无论是计算良品率还是进行其他质量控制相关的操作都能够提供良好的数据支持。

具体查询技巧及良率计算方法参考文章：

SQL进阶技巧：如何根据工业制程参数计算良品率？

想要了解更多数字化建设问题，参考专栏

往期精彩

数势科技指标平台， 让数据产生最大价值

数据科学与SQL：如何利用SQL计算线性回归系数？

SQL进阶技巧：如何利用SQL巧解公务员逻辑推理题？【修正版】

SQL进阶技巧：如何实现分钟级的趋势图？

数据科学与SQL：组距分组分析 | 区间分布问题

SQL进阶技巧：非等值连接--单向近距离匹配

更多内容请点击“阅读原文”~~