PostgreSQL日历会议应用最优数据库表结构设计咨询

现有设计的核心逻辑可行，能覆盖最基础的固定排班+预约需求，但存在几个明显缺陷，会影响后续功能扩展和查询效率：

• staff_availability仅支持周度重复排班，无法覆盖临时调班、请假、特定日期加班等非固定场景；缺少字段级约束和索引，容易产生重叠排班、时间逻辑错误（如开始时间晚于结束时间）等脏数据。
• meetings表缺少状态枚举约束，容易存入无效状态值；没有针对员工+时间维度的索引和重叠校验，高并发下容易出现同个员工同一时段被重复预约的问题；已取消、改期的旧会议没有明确的过滤标识，计算空闲时段时容易误判为占用。

对原有两张表做少量调整即可，不需要额外引入冗余表：

优化后的staff_availability表 #

配套约束和索引：

• 加检查约束CHECK (from_time < to_time)，避免时间逻辑错误
• 加联合索引idx_staff_recurring (staff_id, day_of_week, is_available, from_time, to_time)，加快周度排班查询
• 加联合索引idx_staff_specific (staff_id, specific_date, is_available, from_time, to_time)，加快临时排班查询

优化后的meetings表 #

配套约束和索引：

• rescheduled_meeting_id加外键关联本表id，删除时置空
• 加联合索引idx_staff_meeting (staff_id, status, from_timestamp, to_timestamp)，加快员工指定时段的预约查询
• 数据库支持部分索引的话（MySQL8.0+、PostgreSQL等），加条件唯一索引UNIQUE KEY uniq_staff_valid_meeting (staff_id, from_timestamp, to_timestamp) WHERE status != 'cancelled'，避免有效会议完全重叠

核心逻辑是先拆分出当天所有30分钟粒度的候选时段，再逐一校验该时段是否存在至少一个员工处于可服务状态、且无已预约会议占用。以MySQL8.0+为例，可直接用递归CTE完成全流程查询，示例SQL如下：

-- 替换此处的查询日期即可
SET @query_date = '2022-07-10';

WITH RECURSIVE time_slots AS (
    -- 生成当天0点开始的第一个30分钟时段
    SELECT 
        CAST(CONCAT(@query_date, ' 00:00:00') AS DATETIME) AS slot_start,
        CAST(CONCAT(@query_date, ' 00:30:00') AS DATETIME) AS slot_end
    UNION ALL
    -- 递归生成当天所有30分钟时段，直到次日0点
    SELECT 
        slot_end,
        DATE_ADD(slot_end, INTERVAL 30 MINUTE)
    FROM time_slots
    WHERE slot_end < DATE_ADD(@query_date, INTERVAL 1 DAY)
),
date_meta AS (
    -- 计算查询日期对应的周度规则day_of_week（MySQL原生DAYOFWEEK返回1=周日，减1匹配业务规则）
    SELECT 
        DAYOFWEEK(@query_date) - 1 AS target_dow,
        @query_date AS target_date
),
staff_valid_avail AS (
    -- 合并周度固定可用时段
    SELECT 
        sa.staff_id,
        CAST(CONCAT(dm.target_date, ' ', sa.from_time) AS DATETIME) AS avail_start,
        CAST(CONCAT(dm.target_date, ' ', sa.to_time) AS DATETIME) AS avail_end
    FROM staff_availability sa
    CROSS JOIN date_meta dm
    WHERE sa.is_recurring = 1 
      AND sa.day_of_week = dm.target_dow
      AND sa.is_available = 1
    UNION ALL
    -- 合并当日临时可用时段
    SELECT 
        sa.staff_id,
        CAST(CONCAT(dm.target_date, ' ', sa.from_time) AS DATETIME) AS avail_start,
        CAST(CONCAT(dm.target_date, ' ', sa.to_time) AS DATETIME) AS avail_end
    FROM staff_availability sa
    CROSS JOIN date_meta dm
    WHERE sa.is_recurring = 0 
      AND sa.specific_date = dm.target_date
      AND sa.is_available = 1
    EXCEPT
    -- 排除当日标记为不可用的时段（请假、临时休息）
    SELECT 
        sa.staff_id,
        CAST(CONCAT(dm.target_date, ' ', sa.from_time) AS DATETIME) AS avail_start,
        CAST(CONCAT(dm.target_date, ' ', sa.to_time) AS DATETIME) AS avail_end
    FROM staff_availability sa
    CROSS JOIN date_meta dm
    WHERE sa.is_recurring = 0 
      AND sa.specific_date = dm.target_date
      AND sa.is_available = 0
),
staff_valid_bookings AS (
    -- 查询当日所有非取消、非改期的有效预约
    SELECT 
        m.staff_id,
        m.from_timestamp AS book_start,
        m.to_timestamp AS book_end
    FROM meetings m
    CROSS JOIN date_meta dm
    WHERE m.status NOT IN ('cancelled', 'rescheduled')
      AND m.from_timestamp >= dm.target_date
      AND m.from_timestamp < DATE_ADD(dm.target_date, INTERVAL 1 DAY)
)
-- 筛选出有至少1位员工空闲的可预约时段
SELECT DISTINCT ts.slot_start, ts.slot_end
FROM time_slots ts
WHERE EXISTS (
    SELECT 1
    FROM staff_valid_avail sva
    -- 时段完全落在员工可用时间范围内
    WHERE ts.slot_start >= sva.avail_start
      AND ts.slot_end <= sva.avail_end
      -- 该员工在此时段无有效预约
      AND NOT EXISTS (
          SELECT 1
          FROM staff_valid_bookings svb
          WHERE svb.staff_id = sva.staff_id
            AND svb.book_start < ts.slot_end
            AND svb.book_end > ts.slot_start
      )
)
ORDER BY ts.slot_start;

补充注意事项：

• 如果使用不支持递归CTE的低版本数据库，可以直接在业务代码层生成当天的30分钟时段列表，再传入SQL做后续匹配，逻辑完全一致。
• 用户提交预约时必须加事务和行锁做二次校验，避免高并发下的重复预约问题，不能仅依赖数据库索引做校验，因为预约时长可能跨多个30分钟时段，仅靠唯一索引无法覆盖部分重叠的场景。

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