基于NestJS+MySQL的多供应商电动三轮车保险系统最优表设计咨询

问题背景 #

我正在使用NestJS和MySQL开发一个集成多供应商保险的模块，核心业务为全新电动三轮车的保险办理。目前已设计部分字段并对接了一家供应商，但认为现有方案缺乏可扩展性，特此咨询支持多供应商且具备高扩展性的最优数据库表结构方案。

当前报价表结构 #

CREATE TABLE "lJCWPnNNVy3d95ppLp7M_insurance_quote" (
  "id" bigint NOT NULL AUTO_INCREMENT,
  "quote_enc_id" varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Unique quote identifier',
  "insurance_vendor_enc_id" varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Vendor reference',
  "insurance_product_enc_id" varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Product reference',
  "vehicle_master_enc_id" varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Vehicle reference',
  "vendor_transaction_id" varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Vendor transaction ID',
  "vendor_quote_no" varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Human-readable quote ref — required in proposal API body',
  "vendor_proposal_id" varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Auto-assigned proposal ID — required in proposal API body',
  "premium_amount" decimal(10,2) NOT NULL COMMENT 'Premium amount',
  "premium_breakup" json DEFAULT NULL COMMENT 'Premium breakup',
  "proposal_data" json DEFAULT NULL COMMENT 'Vehicle and Policy input data snapshot',
  "status" enum('CREATED','FAILED','EXPIRED') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'CREATED' COMMENT 'Quote status',
  "created_on" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "created_by" varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'Created by the user',
  "updated_on" timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  "updated_by" varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL COMMENT 'Updated by the user',
  "is_deleted" tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag',
  PRIMARY KEY ("id"),
  UNIQUE KEY "quote_enc_id" ("quote_enc_id"),
  KEY "insurance_vendor_enc_id" ("insurance_vendor_enc_id"),
  KEY "insurance_product_enc_id" ("insurance_product_enc_id"),
  KEY "fk_insurance_quote_created_by" ("created_by"),
  KEY "fk_insurance_quote_updated_by" ("updated_by"),
  KEY "vehicle_master_enc_id" ("vehicle_master_enc_id"),
  KEY "idx_vendor_quote_no" ("vendor_quote_no"),
  KEY "idx_vendor_proposal_id" ("vendor_proposal_id"),
  CONSTRAINT "fk_insurance_quote_created_by" FOREIGN KEY ("created_by") REFERENCES "lJCWPnNNVy3d95ppLp7M_users" ("user_enc_id"),
  CONSTRAINT "fk_insurance_quote_updated_by" FOREIGN KEY ("updated_by") REFERENCES "lJCWPnNNVy3d95ppLp7M_users" ("user_enc_id") ON DELETE SET NULL,
  CONSTRAINT "lJCWPnNNVy3d95ppLp7M_insurance_quote_ibfk_1" FOREIGN KEY ("insurance_vendor_enc_id") REFERENCES "lJCWPnNNVy3d95ppLp7M_insurance_vendor" ("insurance_vendor_enc_id"),
  CONSTRAINT "lJCWPnNNVy3d95ppLp7M_insurance_quote_ibfk_2" FOREIGN KEY ("insurance_product_enc_id") REFERENCES "lJCWPnNNVy3d95ppLp7M_insurance_product" ("insurance_product_enc_id"),
  CONSTRAINT "lJCWPnNNVy3d95ppLp7M_insurance_quote_ibfk_3" FOREIGN KEY ("vehicle_master_enc_id") REFERENCES "lJCWPnNNVy3d95ppLp7M_vehicle_master" ("vehicle_master_enc_id")
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

高扩展性优化方案 #

核心思路是解耦通用字段与供应商专属字段，避免将供应商特有的字段硬编码到主表，同时通过关联表实现灵活扩展，具体分为以下几个模块：

1. 供应商基础信息表（保留现有结构，补充扩展字段） #

CREATE TABLE `insurance_vendor` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `insurance_vendor_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '供应商加密ID',
  `vendor_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '供应商名称',
  `api_config` json NOT NULL COMMENT 'API配置（如请求地址、密钥、签名规则等）',
  `supported_features` set('QUOTE','PROPOSAL','RENEWAL','CLAIM') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '支持的业务功能',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `insurance_vendor_enc_id` (`insurance_vendor_enc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• 新增api_config存储供应商API的差异化配置，避免硬编码到代码中
• supported_features标记供应商支持的业务环节，方便后续流程控制

2. 保险产品表（按供应商+产品维度拆分） #

CREATE TABLE `insurance_product` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `insurance_product_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '产品加密ID',
  `insurance_vendor_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联供应商ID',
  `product_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '产品名称',
  `product_type` enum('THIRD_PARTY','COMPREHENSIVE') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '产品类型（第三方/全险）',
  `vehicle_type` enum('ELECTRIC_TRICYCLE') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '适用车辆类型',
  `coverage_details` json NOT NULL COMMENT '保障范围详情',
  `premium_calculation_rules` json NOT NULL COMMENT '保费计算规则（如费率公式、免赔额等）',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `insurance_product_enc_id` (`insurance_product_enc_id`),
  KEY `insurance_vendor_enc_id` (`insurance_vendor_enc_id`),
  CONSTRAINT `fk_product_vendor` FOREIGN KEY (`insurance_vendor_enc_id`) REFERENCES `insurance_vendor` (`insurance_vendor_enc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• 每个供应商的产品独立存储，支持不同供应商的产品规则差异化
• premium_calculation_rules存储供应商特有的保费计算逻辑，后续可通过NestJS服务解析执行

3. 报价主表（仅保留通用核心字段） #

CREATE TABLE `insurance_quote` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `quote_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '报价加密ID',
  `insurance_vendor_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联供应商ID',
  `insurance_product_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联产品ID',
  `vehicle_master_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联车辆ID',
  `premium_amount` decimal(10,2) NOT NULL COMMENT '总保费',
  `status` enum('CREATED','FAILED','EXPIRED','ACCEPTED') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'CREATED' COMMENT '报价状态',
  `expiry_time` timestamp NOT NULL COMMENT '报价过期时间',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(100) COLLATE utf8mb3_unicode_ci NOT NULL COMMENT '创建人',
  `updated_on` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(100) COLLATE utf8mb3_unicode_ci DEFAULT NULL COMMENT '更新人',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '软删除标记',
  PRIMARY KEY (`id`),
  UNIQUE KEY `quote_enc_id` (`quote_enc_id`),
  KEY `insurance_vendor_enc_id` (`insurance_vendor_enc_id`),
  KEY `insurance_product_enc_id` (`insurance_product_enc_id`),
  KEY `vehicle_master_enc_id` (`vehicle_master_enc_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `fk_quote_vendor` FOREIGN KEY (`insurance_vendor_enc_id`) REFERENCES `insurance_vendor` (`insurance_vendor_enc_id`),
  CONSTRAINT `fk_quote_product` FOREIGN KEY (`insurance_product_enc_id`) REFERENCES `insurance_product` (`insurance_product_enc_id`),
  CONSTRAINT `fk_quote_vehicle` FOREIGN KEY (`vehicle_master_enc_id`) REFERENCES `vehicle_master` (`vehicle_master_enc_id`),
  CONSTRAINT `fk_quote_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_enc_id`),
  CONSTRAINT `fk_quote_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_enc_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• 移除原表中供应商专属字段（如vendor_transaction_id、vendor_quote_no等），通过关联表存储
• 新增expiry_time明确报价有效期，替代模糊的状态判断

4. 供应商报价扩展表（存储专属字段） #

CREATE TABLE `insurance_quote_vendor_extension` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `quote_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联报价ID',
  `vendor_transaction_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '供应商交易ID',
  `vendor_quote_no` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '供应商报价编号',
  `vendor_proposal_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '供应商投保单号',
  `vendor_specific_data` json DEFAULT NULL COMMENT '其他供应商专属字段（如折扣码、附加条款ID等）',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `quote_enc_id` (`quote_enc_id`),
  CONSTRAINT `fk_quote_extension_quote` FOREIGN KEY (`quote_enc_id`) REFERENCES `insurance_quote` (`quote_enc_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• 每个报价对应一条扩展记录，新增供应商专属字段时无需修改主表结构
• vendor_specific_data用于存储临时或低频使用的供应商特有字段，避免频繁加表字段

5. 保费拆分明细表（替代原JSON字段） #

CREATE TABLE `insurance_premium_breakup` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `quote_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联报价ID',
  `breakup_item` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '拆分项名称（如基础保费、交强险、附加险等）',
  `amount` decimal(10,2) NOT NULL COMMENT '拆分项金额',
  `description` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '拆分项说明',
  PRIMARY KEY (`id`),
  KEY `quote_enc_id` (`quote_enc_id`),
  CONSTRAINT `fk_premium_breakup_quote` FOREIGN KEY (`quote_enc_id`) REFERENCES `insurance_quote` (`quote_enc_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• 用结构化表替代JSON存储保费拆分，方便后续统计、查询和扩展
• 支持不同供应商的保费拆分项差异化

6. 投保数据快照表（替代原JSON字段） #

CREATE TABLE `insurance_proposal_snapshot` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `quote_enc_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联报价ID',
  `data_type` enum('VEHICLE','POLICY','INSURED') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '数据类型',
  `data_content` json NOT NULL COMMENT '快照内容',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `quote_enc_id` (`quote_enc_id`),
  CONSTRAINT `fk_proposal_snapshot_quote` FOREIGN KEY (`quote_enc_id`) REFERENCES `insurance_quote` (`quote_enc_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• 将投保数据按类型拆分存储，便于后续针对不同维度的数据进行查询和复用
• 避免单个JSON字段过于庞大，提高数据可读性和维护性

方案优势 #

1. 高扩展性：新增供应商时，只需在insurance_vendor添加记录，扩展专属字段只需更新insurance_quote_vendor_extension或其JSON字段，无需修改主表结构
2. 低耦合：通用逻辑与供应商专属逻辑分离，NestJS服务可通过供应商ID分支处理不同的业务流程
3. 可维护性：结构化存储替代JSON，便于数据查询、统计和审计，降低后续维护成本
4. 灵活性：产品规则、API配置均通过数据库存储，无需修改代码即可调整供应商业务逻辑

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