数据库员工分时段可用性数据的高效存储建模咨询

先直接说你列的两个方案的硬伤：

• 范式化的单时段单记录方案（EmpFK | Day | Period | Availability）：单员工固定占25行，查询单员工整周可用性要拉25条记录，做全公司某时段可用人数统计时需要全表匹配Day、Period两个字段，员工规模上来后IO开销很高，且每条记录重复存储EmpFK，存储浪费明显。
• 宽表方案（EmpFK | Mon1 | Mon2 | .... | Fri5）：字段完全硬编码，后续只要调整时段规则（比如新增周六排班、每天拆成6个时段）就得改表结构，做跨时段统计时要写大量重复的字段判断逻辑，维护成本极高，扩展性极差。

最优落地方案：位掩码存储 #

你这个场景里每个时段的状态只有True/False两种布尔值，25个时段刚好对应25个二进制位，用一个整数字段就能存下单个员工单周的全量可用性数据，冗余度、查询性能、扩展性都远好于上面两个方案。

表结构 #

CREATE TABLE employee_availability (
  emp_id INT NOT NULL COMMENT '员工ID',
  week_start DATE NOT NULL COMMENT '当周周一日期，用于存储历史周的可用性数据',
  avail_mask INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '25位位掩码，存储全周时段可用性',
  PRIMARY KEY (emp_id, week_start)
)

映射规则 #

提前给每个时段分配固定的bit偏移位即可，比如：

• Mon1 对应第0位，位值为 1 << 0 = 1，位值为1代表可用，0代表不可用
• Mon2 对应第1位，位值为 1 << 1 = 2
• Mon3 对应第2位，位值为 1 << 2 = 4
• ……
• Fri5 对应第24位，位值为 1 << 24 = 16777216

常用操作写法 #

• 给员工设置Mon2可用、其余时段暂不可用：

  UPDATE employee_availability SET avail_mask = 2 WHERE emp_id = ? AND week_start = ?
  

• 查询周三第2时段所有可用的员工：

  SELECT emp_id FROM employee_availability WHERE avail_mask & 128 = 128
  

• 给员工批量追加多个可用时段（比如同时开放周二1、周二2时段）：

  UPDATE employee_availability SET avail_mask = avail_mask | (32 | 64) WHERE emp_id = ? AND week_start = ?
  

方案优势 #

• 存储成本极低：单个员工单周仅占1行记录，无重复字段，存储整周状态的整数字段仅占4字节，比前两种方案省90%以上的存储空间
• 查询性能拉满：位运算是数据库原生支持的内存级计算，不管是查单个员工的全周可用性，还是批量筛选某时段可用的员工，都不需要扫描多行、也不需要判断多个字段，响应速度极快
• 扩展性足够：如果后续要新增时段，只要总时段数不超过32个用INT、不超过64个换BIGINT即可，完全不需要改表结构，只要补充新时段和bit位的映射关系就行；要存历史周数据只要靠联合主键里的week_start字段区分即可，不会产生冲突。

特殊场景备选 #

如果后续业务需要给每个时段的可用性加附加属性（比如不可用原因是请假/外出、标记提交时间、审批记录），那直接选第一种单时段单记录的范式化设计即可，这种场景下每行记录承载独立的业务属性，不存在冗余问题。

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