基于开放/售罄名额数据的单表数据库最优设计方案咨询

你目前构思的「所有ID存同一列、每个参观季新增两列存数据」的宽表方案并不适合长期业务发展，本质属于违反数据库设计第一范式的反模式设计：后续参观季持续增长时，你需要反复执行DDL修改表结构新增列，跨季统计、动态查询时需要拼接大量列名，扩展能力极差。

推荐表结构设计 #

采用窄表长数据的设计思路，单条记录唯一对应「某一个参观场所+某一个参观季」的名额数据，完全不需要随业务增长修改表结构，字段设计如下：

• id：自增主键，BIGINT类型，单表场景用自增主键维护、查询效率最高
• venue_id：参观场所唯一标识，和你现有ID_01这类标识的字段类型匹配即可，建议加普通索引
• season_id：参观季序号，INT类型，从1开始递增对应第N个参观季，建议加普通索引
• available_quota：INT类型，存储对应场所对应参观季的开放总名额
• sold_quota：INT类型，存储对应场所对应参观季的已售名额
• 可选扩展字段：可根据分析需求加season_start_time、season_end_time字段存储参观季的时间范围，方便按时间维度做筛选统计

以MySQL为例，建表语句参考：

CREATE TABLE `venue_season_quota` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `venue_id` VARCHAR(32) NOT NULL COMMENT '参观场所唯一ID',
  `season_id` INT NOT NULL COMMENT '参观季序号',
  `available_quota` INT NOT NULL DEFAULT 0 COMMENT '当季开放总名额',
  `sold_quota` INT NOT NULL DEFAULT 0 COMMENT '当季已售名额',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_venue_season` (`venue_id`, `season_id`),
  KEY `idx_season_id` (`season_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='参观场所各季名额数据表';

其中uk_venue_season联合唯一索引用来避免同一个场所同一个参观季重复插入数据。

方案优势 #

• 零结构变更成本：后续新增参观季、新增场所都只需要插入对应记录，不需要执行改表操作，不管场所数、参观季数增长到什么规模都能适配
• 查询分析灵活：所有统计需求都可以通过标准SQL实现，不需要动态拼接列名。你给出的示例数据插入语句参考：

INSERT INTO `venue_season_quota` (`venue_id`, `season_id`, `available_quota`, `sold_quota`)
VALUES
('ID_01', 1, 25, 24),
('ID_01', 2, 30, 30),
('ID_01', 3, 30, 30),
('ID_02', 1, 25, 15),
('ID_02', 2, 20, 18),
('ID_02', 3, 25, 21),
('ID_03', 1, 25, 10),
('ID_03', 2, 15, 15),
('ID_03', 3, 20, 13);

常见分析场景的SQL实现非常简洁：

• 查第3季所有场所的售罄率：

SELECT 
  venue_id,
  ROUND(sold_quota/available_quota*100, 2) AS sold_rate
FROM venue_season_quota
WHERE season_id = 3;

• 查连续3个季满额售罄的场所：

SELECT venue_id
FROM venue_season_quota
WHERE season_id IN (1,2,3)
GROUP BY venue_id
HAVING SUM(sold_quota = available_quota) = 3;

• 维护成本极低：对比你最早使用的「每个ID单独建表」方案，不会出现随场所数增长表数量爆炸的问题，备份、迁移、权限管控都只需要操作单表。

两类旧方案的核心问题 #

• 单场所单表方案：表数量随场所数线性增长，跨场所统计需要关联几十上百张表，维护成本随业务规模指数级上升
• 季增列宽表方案：表宽度随参观季数持续增长，主流关系型数据库对单表列数有上限，且宽表查询IO开销高，动态拼列的代码维护成本极高，后续如果要给参观季增加属性（比如参观季主题、开放规则）完全无法扩展

内容的提问来源于stack exchange，提问作者Cauã Almeida