如何构建高效无错的酒店价格与预订数据库？

针对酒店客房价格随日期、入住人数、星期变化的需求，结合两种存储方式的优缺点，推荐分层存储+约束校验+高效查询的方案，兼顾录入便捷性、查询效率与数据完整性。

一、核心表结构设计 #

1. 基础价格范围表（处理大范围通用规则） #

用于存储长期、批量的价格规则，支持平日/周末差异化定价，减少重复数据。

CREATE TABLE room_price_ranges (
    id INT PRIMARY KEY AUTO_INCREMENT,
    room_id INT NOT NULL,
    date_start DATE NOT NULL, -- 范围起始日期（包含）
    date_end DATE NOT NULL, -- 范围结束日期（不包含，避免重叠）
    base_price_1 DECIMAL(10,2) NOT NULL, -- 1人入住基础价
    base_price_2 DECIMAL(10,2) NOT NULL, -- 2人入住基础价
    weekend_surcharge DECIMAL(10,2) DEFAULT 0, -- 周末附加费（可选，替代单独周末价格字段）
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    INDEX idx_room_date (room_id, date_start, date_end)
);

• 关键设计：date_end设为不包含，比如范围2023-01-01至2023-01-03仅覆盖1月1日、2日，下一个范围可从2023-01-03开始，无缝衔接无重叠。

2. 单日价格覆盖表（处理特殊日期） #

用于存储管理员单独设置的单日特殊价格，优先级高于基础范围表，解决单日录入需求。

CREATE TABLE room_daily_prices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    room_id INT NOT NULL,
    target_date DATE NOT NULL,
    price_1 DECIMAL(10,2) NOT NULL,
    price_2 DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    UNIQUE KEY uk_room_date (room_id, target_date), -- 确保单房间单日价格唯一
    INDEX idx_room_target (room_id, target_date)
);

3. 客房基础信息表（补充默认价格） #

在客房表中增加默认价格字段，作为空白日期的兜底：

CREATE TABLE rooms (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    default_price_1 DECIMAL(10,2) NOT NULL, -- 1人入住默认价
    default_price_2 DECIMAL(10,2) NOT NULL, -- 2人入住默认价
    -- 其他客房属性字段
);

二、数据完整性保障 #

1. 避免日期范围冲突 #

插入/更新基础价格范围时，通过业务逻辑或数据库触发器检查：

-- 检查新范围是否与已有范围重叠
SELECT COUNT(*) FROM room_price_ranges 
WHERE room_id = ? 
AND date_start < ? -- 新范围的date_end
AND date_end > ?; -- 新范围的date_start

若结果>0，拒绝插入，确保无重叠范围。

2. 消除空白日期 #

• 兜底机制：查询时优先取单日价格，其次匹配基础范围，最后用客房默认价格填充。
• 后台监控：定时任务扫描全量日期，标记无价格覆盖的时间段，提醒管理员补充。

三、高效查询实现 #

用户预订多天时，通过SQL递归CTE生成日期范围，一次性关联所有价格表，避免代码层逐天匹配：

WITH RECURSIVE booking_dates AS (
    SELECT '2023-01-01' AS book_date
    UNION ALL
    SELECT DATE_ADD(book_date, INTERVAL 1 DAY)
    FROM booking_dates
    WHERE book_date < '2023-01-07'
)
SELECT 
    bd.book_date,
    -- 优先级：单日价格 > 基础范围+周末附加费 > 客房默认价
    COALESCE(dp.price_1, 
             rp.base_price_1 + CASE WHEN WEEKDAY(bd.book_date) IN (5,6) THEN rp.weekend_surcharge ELSE 0 END,
             r.default_price_1) AS price_1,
    COALESCE(dp.price_2, 
             rp.base_price_2 + CASE WHEN WEEKDAY(bd.book_date) IN (5,6) THEN rp.weekend_surcharge ELSE 0 END,
             r.default_price_2) AS price_2
FROM booking_dates bd
LEFT JOIN room_daily_prices dp ON bd.book_date = dp.target_date AND dp.room_id = 1
LEFT JOIN room_price_ranges rp ON bd.book_date >= rp.date_start AND bd.book_date < rp.date_end AND rp.room_id = 1
LEFT JOIN rooms r ON r.id = 1;

四、管理员操作优化 #

• 批量录入工具：支持选择日期范围设置价格，自动检查冲突后插入基础范围表；单日价格直接录入覆盖表。
• 可视化日历：后台用不同颜色标记基础范围、单日价格、空白日期，直观展示价格覆盖情况。

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