多分类字段地图标记应用数据库建模合理性及优化咨询

你当前采用单张标记表存储全部分类字段的建模方式，仅适合分类完全固定、无后续迭代需求的极小体量demo场景，长期来看不具备生产可用性。你目前发现的每条标记记录必然产生空值只是最表层的缺陷，设计本身的抗迭代能力、数据一致性保障能力都存在明显短板。

• 存储冗余效率低：后续新增的分类越多，单表内的空字段占比会越高，既浪费存储空间，也会拉低常规查询的执行效率
• 扩展成本极高：每新增一个标记分类、调整某类标记的填写字段，都需要对标记主表执行DDL操作修改表结构，对于迭代频繁的移动端业务，频繁改表会带来非常高的线上风险
• 数据一致性难保障：不同分类的字段非空规则、格式校验逻辑全部需要在业务代码层实现，无法通过数据库层的约束做兜底，非常容易写入不符合规则的脏数据

根据项目所处阶段和后续业务规划，可以选以下三种方案，改造成本和灵活性依次提升：

方案1：垂直分表（适合分类固定、性能要求高的场景） #

• 建markers主表，仅存储所有标记共有的公共属性：标记ID、关联分类ID、经纬度坐标、创建用户、创建时间、更新时间这类全分类通用字段
• 为每个分类单独建一张扩展表，比如marker_poi存储兴趣点分类的专属字段（标题、描述、类型），marker_obstacle存储障碍物上报分类的专属字段（描述、类型），所有扩展表通过marker_id字段和主表做一对一关联
• 优势：完全不存在空值冗余，可以直接在数据库层给每个分类的字段加非空、格式约束，查询单分类数据时关联对应扩展表即可，查询性能很高；劣势是分类数量变多后表的数量会同步增加，跨分类批量查询时需要关联多张表，写SQL会更繁琐

方案2：JSON字段折中方案（适合项目初期、需要平衡开发效率和灵活性的场景，推荐度最高） #

• 保留markers主表存储公共属性，新增一个ext_info的JSON类型字段（MySQL 5.7+、PostgreSQL等主流数据库都原生支持），用来存储当前标记所属分类的专属字段键值对
• 额外建两张配置表：categories表存储分类基础信息（分类ID、分类名称、展示图标等），category_fields表存储每个分类对应的字段规则（字段名、字段类型、是否必填、校验规则等），业务层写入数据前，先按照对应分类的字段规则校验ext_info内的内容合法性
• 示例：兴趣点标记的ext_info存储内容为{"title":"山顶观景台","desc":"视野极佳无遮挡","type":"自然景观"}，障碍物上报标记的ext_info存储内容为{"desc":"人行道有施工围挡","type":"施工阻碍"}
• 优势：不需要额外建大量分表，没有空值冗余，开发改造成本极低，主流数据库都支持对JSON内的字段建索引、做条件查询，性能表现远好于通用动态模型；劣势是数据库层无法直接对JSON内部的字段做强制约束，必须在业务层做好校验逻辑，避免脏数据写入

方案3：EAV动态模型（适合后续需要支持用户自定义分类、动态配置字段的场景） #

• 保留markers主表存公共字段、categories表存分类信息、category_fields表存分类字段配置
• 新增marker_field_values表存储标记对应的自定义字段值，核心字段包括：值ID、关联标记ID、关联字段ID、字段值内容
• 逻辑示例：新增「兴趣点」分类时，先在categories表插入分类记录，再在category_fields表给该分类绑定「标题（文本、必填）」「描述（长文本、必填）」「类型（枚举、必填）」三个字段规则；用户提交标记时，先在markers表插入公共字段数据，再把用户填的每个自定义字段值逐条插入marker_field_values表即可
• 优势：完全不需要修改表结构，后台可以动态新增分类、调整任意分类的字段配置，灵活性拉满；劣势是查询单条标记的全量信息需要做两次表关联，批量查询、按自定义字段筛选时性能弱于前两种方案，需要提前做好索引优化

选型建议：项目初期用户规模小、分类数量不超过5个的时候直接选方案2就行，开发速度快，后续要做动态分类能力再迭代到EAV模型即可，没必要一开始就上最复杂的设计。

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