如何使用多列 composite key 设计酒店 rate plan 费率表？

结论

不要按 occupancy 或 contract type 拆列、拆表。建议采用下面的设计：

1. rate_plan 保存房型、餐食、支付方式等费率计划定义。
2. rate_plan_rate 使用窄表结构，一行表示某天、某个 contract、某种 occupancy 的价格。
3. 使用单列 rate_id 作为主键，并为业务字段建立 UNIQUE 约束。
4. 只保存实际配置的价格，不要提前生成所有酒店、日期和类型的笛卡尔积。
5. 如果大量日期的价格相同，可以增加“日期区间价格 + 单日覆盖价格”，减少每日重复数据。
6. booking_details 必须保存成交价格快照，不能只依赖指向费率表的外键。
7. 超过两年的数据应按日期分区迁移到分析库，或保存为对象存储中的 Parquet 文件。不要每天逐行删除，也不建议把 CSV 作为主要归档格式。

在给出的业务规模下，问题不在于“复合主键能不能用”，而在于如何避免产生数百亿条大部分为空或内容重复的数据。

先核对数据量

如果为所有组合生成记录，行数为：

50,000 × 40 × 6 × 4 × 730
= 35,040,000,000

结果是 350.4 亿行，不是 87.6 亿行。

如果把 6 种 occupancy 改成 6 个字段，理论行数为：

50,000 × 40 × 4 × 730
= 5,840,000,000

行数虽然降到了 58.4 亿，但表结构会变得僵化，产生大量 NULL。每增加一种 occupancy，还要同时修改表结构、程序和索引。

如果每个 contract type 使用一张表，并且每行仍只保存一种 occupancy，那么每张表有：

50,000 × 40 × 6 × 730
= 8,760,000,000

如果每种 occupancy 改成独立字段，每张表则有：

50,000 × 40 × 730
= 1,460,000,000

拆表并没有减少数据，只是把同一个问题分散到了多个结构相同的表里。

推荐的数据模型

1. 费率计划定义表

房型、餐食方案、支付方式等属性属于 rate plan，不应在每日价格表中重复保存。

CREATE TABLE rate_plan (
    rate_plan_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    hotel_id           BIGINT NOT NULL,
    room_type_id       BIGINT NOT NULL,
    meal_plan_id       BIGINT NOT NULL,
    payment_mode_id    BIGINT NOT NULL,
    name               VARCHAR(150) NOT NULL,
    active             BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT uq_rate_plan_definition
        UNIQUE (
            hotel_id,
            room_type_id,
            meal_plan_id,
            payment_mode_id
        )
);

同一家酒店能否创建属性完全相同、名称不同的两个 rate plan，要由业务规则决定。如果允许，就需要调整或取消上面的业务唯一约束。

2. 维度表

CREATE TABLE contract_type (
    contract_type_id SMALLINT PRIMARY KEY,
    code             VARCHAR(30) NOT NULL UNIQUE,
    name             VARCHAR(100) NOT NULL
);

CREATE TABLE occupancy_type (
    occupancy_type_id SMALLINT PRIMARY KEY,
    code              VARCHAR(30) NOT NULL UNIQUE,
    adults            SMALLINT NOT NULL,
    children          SMALLINT NOT NULL DEFAULT 0,
    infants           SMALLINT NOT NULL DEFAULT 0
);

不要把 occupancy 直接设计成 rate_single、rate_double、rate_extra_child2 等字段。增加一种 occupancy，应该新增一条维度数据，而不是修改数据库结构。

如果“额外成人”和“额外儿童”本质上是附加费用，而不是入住人数类型，最好将其单独建模为 adjustment，不要与 single、double 混在同一个维度中。

3. 每日费率表

CREATE TABLE rate_plan_rate (
    rate_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    hotel_id           BIGINT NOT NULL,
    rate_plan_id       BIGINT NOT NULL,
    stay_date          DATE NOT NULL,
    contract_type_id   SMALLINT NOT NULL,
    occupancy_type_id  SMALLINT NOT NULL,
    amount             DECIMAL(14, 2) NOT NULL,
    currency_code      CHAR(3) NOT NULL,
    available          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT ck_rate_amount
        CHECK (amount >= 0),

    CONSTRAINT fk_rate_plan
        FOREIGN KEY (rate_plan_id)
        REFERENCES rate_plan (rate_plan_id),

    CONSTRAINT fk_rate_contract
        FOREIGN KEY (contract_type_id)
        REFERENCES contract_type (contract_type_id),

    CONSTRAINT fk_rate_occupancy
        FOREIGN KEY (occupancy_type_id)
        REFERENCES occupancy_type (occupancy_type_id),

    CONSTRAINT uq_rate_business_key
        UNIQUE (
            hotel_id,
            rate_plan_id,
            stay_date,
            contract_type_id,
            occupancy_type_id
        )
);

这里使用了两类键：

• rate_id 是技术主键，方便其他表引用。
• 五个业务字段组成候选键，通过 UNIQUE 防止重复插入同一价格。

如果 rate_plan_id 在整个系统中唯一，并且一定能够确定 hotel_id，那么从纯逻辑上说，唯一约束可以不包含 hotel_id。不过在超大规模系统中，将 hotel_id 放进索引、分区键或分片键，通常更便于数据定位和酒店级查询。

为什么不直接使用复合主键

下面的复合主键在关系模型中完全合法：

PRIMARY KEY (
    rate_plan_id,
    stay_date,
    contract_type_id,
    occupancy_type_id
)

其他表也可以引用它：

FOREIGN KEY (
    rate_plan_id,
    stay_date,
    contract_type_id,
    occupancy_type_id
)
REFERENCES rate_plan_rate (
    rate_plan_id,
    stay_date,
    contract_type_id,
    occupancy_type_id
)

但这样设计会给 booking_details 带来一些问题：

• 每条 booking detail 都要复制多个外键字段。
• 相关索引会更宽。
• ORM 映射和更新逻辑会更复杂。
• 费率规则调整后，不适合修改复合键中的字段。
• 如果以后增加销售渠道、币种或客群等维度，键还会继续膨胀。

因此，常见业务系统更适合采用：

单列 surrogate primary key
+
业务字段 UNIQUE constraint

不过，当数据达到数百亿行时，主键和唯一索引的成本都很高。最终如何选择聚簇键、分区键和二级索引，必须结合具体数据库判断。PostgreSQL、MySQL/InnoDB、SQL Server 和分布式数据库在这方面差异很大，不能只根据逻辑模型确定物理索引顺序。

不要生成不存在的费率

小型酒店没有配置 triple、extra infant 等价格时，不要生成对应记录。

例如，某个 rate plan 只配置了 double occupancy 和 website contract，就只插入实际存在的组合：

INSERT INTO rate_plan_rate (
    hotel_id,
    rate_plan_id,
    stay_date,
    contract_type_id,
    occupancy_type_id,
    amount,
    currency_code
)
VALUES (
    10001,
    900001,
    DATE '2026-09-01',
    2,
    2,
    899.00,
    'CNY'
);

业务层必须明确“没有记录”表示什么，例如：

• 不可售；
• 使用默认 occupancy 价格；
• 继承上一级 contract 价格；
• 需要人工确认。

不要让“记录不存在”同时承担多种含义。如果系统支持价格继承，应当明确建模继承规则。

使用日期区间进一步减少记录

酒店价格可以每天不同，但实际业务中经常会出现连续多天价格相同的情况，例如工作日价、周末价或旺季价。

基础价格可以按日期区间保存：

CREATE TABLE rate_plan_rate_period (
    rate_period_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    hotel_id            BIGINT NOT NULL,
    rate_plan_id        BIGINT NOT NULL,
    contract_type_id    SMALLINT NOT NULL,
    occupancy_type_id   SMALLINT NOT NULL,
    valid_from          DATE NOT NULL,
    valid_to            DATE NOT NULL,
    amount              DECIMAL(14, 2) NOT NULL,
    currency_code       CHAR(3) NOT NULL,

    CONSTRAINT ck_rate_period
        CHECK (valid_to >= valid_from)
);

例如，一条记录可以表示整个淡季：

INSERT INTO rate_plan_rate_period (
    hotel_id,
    rate_plan_id,
    contract_type_id,
    occupancy_type_id,
    valid_from,
    valid_to,
    amount,
    currency_code
)
VALUES (
    10001,
    900001,
    2,
    2,
    DATE '2026-09-01',
    DATE '2026-11-30',
    699.00,
    'CNY'
);

特殊日期可以另外保存到单日覆盖表。查询优先级可以规定为：

单日覆盖价格
→ 日期区间价格
→ rate plan 默认价格
→ 不可售

这种设计可以明显减少重复行，但必须避免同一业务组合出现相互重叠的日期区间。具体如何实现“不允许区间重叠”，取决于所用数据库。PostgreSQL 可以使用 range type 和 exclusion constraint，其他数据库通常需要通过事务、锁或应用层校验实现。

如果价格确实每天都不同，或者下游搜索系统需要直接按日期读取，也可以继续使用每日价格表。此时可以把区间规则展开到有限的未来窗口，例如未来 365 天，而不是一次生成全部理论组合。

booking_details 应保存价格快照

预订不能只保存 rate_id。价格可能会被修改、归档，甚至因纠错而失效，但已经完成的订单必须保留成交时的事实。

CREATE TABLE booking_details (
    booking_detail_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    booking_id          BIGINT NOT NULL,
    rate_id             BIGINT NULL,
    hotel_id            BIGINT NOT NULL,
    rate_plan_id        BIGINT NOT NULL,
    stay_date           DATE NOT NULL,
    contract_type_id    SMALLINT NOT NULL,
    occupancy_type_id   SMALLINT NOT NULL,

    quoted_amount       DECIMAL(14, 2) NOT NULL,
    tax_amount          DECIMAL(14, 2) NOT NULL DEFAULT 0,
    currency_code       CHAR(3) NOT NULL,
    rate_snapshot       JSON NULL,

    CONSTRAINT fk_booking_rate
        FOREIGN KEY (rate_id)
        REFERENCES rate_plan_rate (rate_id)
);

建议保存以下信息：

• 原始房价；
• 税费及服务费；
• 币种；
• occupancy；
• contract 或销售渠道；
• 餐食和取消政策；
• 折扣、促销及其计算结果；
• 必要时保存完整 JSON 快照。

rate_id 可以用于追溯价格来源，但订单金额应以 booking 中保存的快照为准。费率数据归档到另一数据库后，跨数据库外键通常无法继续成立，因此是否为 rate_id 保留强制外键，需要结合归档方案决定。

分区和索引策略

这种规模的数据不适合放在一张普通单表中，再随意添加几个索引。至少应按 stay_date 进行月度或季度分区，并将 hotel_id 作为分片字段或索引的前导字段之一。

典型查询可以考虑使用下面的索引：

CREATE INDEX ix_rate_search
ON rate_plan_rate (
    hotel_id,
    stay_date,
    rate_plan_id,
    contract_type_id,
    occupancy_type_id
);

索引顺序必须根据真实查询确定。例如，搜索请求通常已经知道酒店和入住日期，那么将 hotel_id, stay_date 放在前面就比较合理。

不要为每个字段分别创建单列索引。在数十亿行数据上，每个无效索引都会增加明显的存储和写入成本。

也不应把所有酒店的数据放进同一个无限增长的数据库实例。数据达到数十亿甚至数百亿行时，通常需要结合以下手段：

• 按日期分区；
• 按 hotel_id 分片；
• 读写分离；
• 为搜索侧增加缓存，或使用专门的 availability/rate 查询存储；
• 将分析数据与交易数据分离。

最终方案取决于数据库类型、每日更新量、查询延迟目标和并发量，不能只看总行数。

两年以上数据如何归档

不要每天执行大批量的逐行删除：

DELETE FROM rate_plan_rate
WHERE stay_date < ...;

这种操作会产生大量日志、锁、索引维护和空间回收压力。更稳妥的做法是按月分区：

1. 在线交易库保留最近两年需要使用的分区。
2. 将到期分区从在线表中 detach 或 switch 出来。
3. 校验行数、金额汇总和文件校验值。
4. 写入分析仓库，或导出为按年月、酒店分区的 Parquet 文件。
5. 确认归档数据可以查询和恢复后，再删除在线分区。

冷数据可以根据使用方式选择存储位置：

• 需要频繁分析时，放入数据仓库或分析型数据库；
• 主要用于长期保存和批量分析时，保存为对象存储中的 Parquet；
• 仍需低延迟逐条查询时，使用独立的 archive database。

CSV 适合临时交换，但不适合作为主要归档格式。它缺少可靠的数据类型、压缩和列裁剪能力，schema 演进也不方便。Parquet 更适合大规模历史数据分析。

归档边界也要明确。stay_date、价格生效日期和记录创建时间是不同的概念。如果业务要求“始终可销售未来两年”，就不能简单地按照 created_at 删除数据。通常应根据 stay_date 和业务保留政策管理分区。

最终选择

三个方案中，第一种“每个 occupancy 一行”的方向最合理，但不应提前生成所有理论组合。最终结构应为：

rate_plan
    └── rate_plan_rate
          一行 = rate plan + stay date + contract + occupancy

并使用：

rate_id PRIMARY KEY
UNIQUE (hotel_id, rate_plan_id, stay_date, contract_type_id, occupancy_type_id)

如果数据量仍然过大，应优先采用稀疏存储、日期区间、分区和按酒店分片，而不是把 occupancy 改成字段，或按 contract type 复制多张表。订单需要保存成交价格快照，历史费率则通过分区迁移到分析库，或归档为 Parquet 文件。

备注：内容仅供参考。