Oracle APEX(21.2)数据库设计：拆分数字存储与规范化方案抉择

需求背景 #

现有consent form表，创建后需分配给不同目标组：

• 学区金字塔
• 学校层级（高中/初中/小学）
• 年级（1-12）
• 特定学校（按School ID）

单个表单可关联多个金字塔（最多5个）或学校（最多10个），计划借助Oracle APEX的APEX_STRING.SPLIT_NUMBERS接口实现，现对比两种设计方案的优劣并寻求实施建议。

方案一：扁平化设计 #

设计思路 #

将各目标组ID以数字列表（如2:12:13）存储在对应字段，用APEX_STRING.SPLIT_NUMBERS拆分列表，结合MEMBER OF做SQL校验；同时保留school_wide（Y/N）字段支持全校范围设置。

表结构 #

dpc_consent_form_target {
  target_id                 number         [primary key]
  consent_form_id           number         [ref: > dpc_consent_form.consent_form_id]
  school_wide               char(1)        [chk 'Y','N']
  pyramid_id                number         -- 存储以冒号分隔的金字塔ID列表
  school_id                 number         -- 存储以冒号分隔的学校ID列表
  school_level_id           number         -- 存储以冒号分隔的学校层级ID列表
  grade_id                  number         -- 存储以冒号分隔的年级ID列表
}

优劣分析 #

优势

• 结构直观，表单与目标组的关联关系在单条记录中体现，查询无需多表关联，实现简单
• 借助APEX的APEX_STRING.SPLIT_NUMBERS可快速拆分列表，配合MEMBER OF能便捷完成权限或归属校验
• school_wide字段单独处理，逻辑清晰，无需额外关联

弊端

• 违反数据库规范化原则，字段存储多值，无法通过外键约束保证数据完整性（比如无法直接校验金字塔ID是否存在于对应字典表）
• 索引优化困难：多值字段无法创建有效索引，数据量较大时，拆分查询的性能会显著下降
• 扩展性差：新增目标类型（如班级）需修改表结构添加新字段，维护成本高
• 数据维护复杂：修改单个目标ID时，需要拆分、修改、拼接字符串，容易出错，且无法追踪单个目标的变更历史

方案二：规范化设计 #

设计思路 #

通过dpc_consent_form_target表存储单个目标记录，搭配dpc_ref_target_type字典表区分目标类型，每个表单的多个目标组对应多条记录。

表结构 #

关联表dpc_consent_form_target

table dpc_consent_form_target {
  target_id                 number         [primary key]
  consent_form_id           number         [ref: > dpc_consent_form.consent_form_id]
  target_type_id            number         [ref: > dpc_ref_target_type.target_type_id]
  target_value_id           number         -- 对应目标类型的ID值
  created_by                varchar2(50)
  created_date              timestamp
  updated_by                varchar2(50)
  updated_date              timestamp
}

目标类型字典表dpc_ref_target_type

table dpc_ref_target_type {
  target_type_id            number         [primary key]
  target_type_name          varchar2(50)
  active_flag               char(1)
  created_by                varchar2(50)
  created_date              timestamp
  updated_by                varchar2(50)
  updated_date              timestamp
  deleted_by                varchar2(50)
  deleted_date              timestamp
}

目标类型值定义

1 - pyramid_id（学区金字塔）
2 - school_level_id（学校层级）
3 - school_id（特定学校）
4 - grade_id（年级）
5 - school_wide（全校范围）

school_wide场景处理 #

对于school_wide（全校范围），可将target_value_id设为固定值（如0或-1），代表“全校”，业务逻辑中判断：当target_type_id=5时，忽略target_value_id或直接认定为全校范围。同时可在字典表中对该类型添加说明，明确其无需关联具体ID。

优劣分析 #

优势

• 符合数据库规范化范式，数据完整性强：可通过外键约束target_value_id关联对应字典表（除school_wide外），避免无效ID
• 扩展性好：新增目标类型只需在dpc_ref_target_type中添加记录，无需修改表结构
• 便于索引优化：可针对consent_form_id、target_type_id等字段创建联合索引，大幅提升查询性能
• 支持变更追踪：每条目标记录有独立的创建/更新日志，可追踪单个目标的变更历史
• 数据维护灵活：修改单个目标只需操作单条记录，无需处理字符串拼接

弊端

• 查询时需要多表关联，SQL语句相对复杂，尤其是统计表单关联的所有目标组时，需聚合查询
• 初始开发工作量略大，需处理多记录的增删改逻辑（比如表单关联多个金字塔时，要插入多条记录）

实施建议 #

1. 优先选择规范化方案：

   • 若系统数据量较大、未来有扩展需求（新增目标类型），规范化方案的可维护性和性能优势会逐渐凸显
   • 配合APEX的动态表单或表格组件，可快速实现多目标记录的批量增删改，降低开发复杂度
   • school_wide场景通过固定值标记即可，业务逻辑中单独判断，无需特殊处理表结构

2. 扁平化方案的适用场景：

   • 仅适用于小型系统、数据量少且确定未来不会扩展目标类型的场景
   • 若选择此方案，需额外编写校验逻辑（如在APEX的提交过程中，拆分列表并验证每个ID的有效性），同时做好数据备份和异常处理

内容的提问来源于stack exchange，提问作者McRivers