数据库客房管理系统的表结构设计与数据关系梳理

客房管理系统看起来像是“房间、客人、订单”三张表的问题，真正容易出错的却是房间何时可订、一次预订包含几间房、取消后怎样释放房间，以及入住和离店状态如何与日期保持一致。表结构如果只记录一个“房间状态”，短期内似乎方便，遇到提前预订、多人入住、临时维修和历史查询时就会暴露问题。

本文以 PostgreSQL 为例，从业务边界出发梳理数据关系，并提供一套可执行的基础 DDL。示例适合单店或中小型住宿业务的预订与房态管理起步，不包括渠道对接、复杂计价、会员积分、发票、集团多门店等完整功能。实际系统应根据业务流程增补，而不是把示例表直接当成完整产品。

一、先确定系统要管理什么

客房管理常见的业务至少有三类：

• 客房资料管理：维护房型、房间编号、楼层、可售与停用状态等信息。
• 预订与入住：记录谁订了什么、入住和离店日期、实际分配到哪间房，以及预订是否取消或已经入住。
• 经营记录：保存收款、退款、维修、清洁和操作日志，便于对账和追溯。

需要先区分两个容易混淆的概念：房间的静态状态与某段时间内能否预订。例如，房间可能处于“启用”状态，但下周已经被预订；也可能没有预订，却因维修暂时不可售。仅靠 rooms.status 一个字段无法表达这些时间关系。

基础建模采用以下约定：

• 房型描述可复用的产品属性，如名称、床型和标准入住人数。
• 房间是实际可分配的实体，有唯一房间编号，并归属一个房型。
• 预订单记录一次业务预订及联系人信息。
• 预订房间记录这张预订单包含哪些房间，以及每间房对应的入住区间。
• 收款记录独立保存，因为一张预订单可能多次收款、退款或拆分支付。

二、关系梳理：一张订单为什么不应只放一个房间编号

如果业务明确保证“一张预订单只能订一间房”，可以把房间编号和入住日期直接放在预订表中。但很多住宿场景允许一人一次预订两间或更多房间，也可能由同一联系人代订。此时将 room_id 放在预订主表，会导致一张订单只能表示一个房间；用 room_id_1、room_id_2 这样的列来扩展，则既限制数量，也不便查询。

更通用的结构是：

• room_types 与 rooms：一对多，一个房型可以有多间实际房间。
• reservations 与 reservation_rooms：一对多，一张预订单可以包含多间房。
• rooms 与 reservation_rooms：一对多，一间房在不同日期可以出现在不同预订中，但有效入住区间不能相互重叠。
• reservations 与 payments：一对多，一张订单可关联多条收款或退款记录。

示例把联系人放在预订单上，代表“由谁下单或负责联系”，不等于所有入住人的完整身份档案。若系统必须管理每位实际入住人，应增加入住人表，以及预订房间与入住人之间的关联；不要把多个姓名用逗号拼进一个字段。

三、关键边界：入住日期通常采用左闭右开

预订入住日期和离店日期通常表示为 [check_in, check_out)：入住日计入占用，离店日不计入。比如 8 月 1 日入住、8 月 4 日离店，占用的是 1 日、2 日、3 日这三晚；另一个客人可以在 4 日入住。

使用左闭右开区间有两个好处：连续两单可以在离店日和入住日衔接；重叠判断也不需要人为减一天。示例通过 PostgreSQL 的 daterange 表达日期区间，并用排斥约束防止同一间房的有效分配区间重叠。

这套规则针对的是“实际房间已经分配”的情况。若预订时只确定房型、尚未确定具体房间，就不能把某间房提前写进分配表。此时需要另行设计房型库存或按日期计算可售量，并在事务中处理并发扣减。房型库存问题与具体房间冲突不是同一个问题。

四、PostgreSQL 建表示例

以下脚本按顺序执行。它使用 PostgreSQL 的 btree_gist 扩展，以便在 GiST 排斥约束中比较整数房间编号。当前数据库账号必须有权限创建该扩展；在托管数据库中，可能需要管理员预先启用。该脚本不是数据库迁移工具，不应在已有生产数据的库中不经评估反复执行。

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_types (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code            TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    max_occupancy   SMALLINT NOT NULL CHECK (max_occupancy > 0),
    base_rate       NUMERIC(12, 2) NOT NULL CHECK (base_rate >= 0),
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE rooms (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_number     TEXT NOT NULL UNIQUE,
    room_type_id    BIGINT NOT NULL REFERENCES room_types(id)
                        ON UPDATE RESTRICT ON DELETE RESTRICT,
    floor_number    SMALLINT,
    room_state      TEXT NOT NULL DEFAULT 'active'
                        CHECK (room_state IN
                               ('active', 'out_of_service', 'retired')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE reservations (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    reservation_code TEXT NOT NULL UNIQUE,
    contact_name    TEXT NOT NULL,
    contact_phone   TEXT,
    status          TEXT NOT NULL DEFAULT 'pending'
                        CHECK (status IN
                               ('pending', 'confirmed', 'checked_in',
                                'checked_out', 'cancelled', 'no_show')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE reservation_rooms (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    reservation_id  BIGINT NOT NULL REFERENCES reservations(id)
                        ON UPDATE RESTRICT ON DELETE RESTRICT,
    room_id         BIGINT NOT NULL REFERENCES rooms(id)
                        ON UPDATE RESTRICT ON DELETE RESTRICT,
    check_in        DATE NOT NULL,
    check_out       DATE NOT NULL,
    allocation_state TEXT NOT NULL DEFAULT 'held'
                        CHECK (allocation_state IN
                               ('held', 'confirmed', 'occupied',
                                'released', 'completed')),
    nightly_rate    NUMERIC(12, 2) NOT NULL CHECK (nightly_rate >= 0),
    stay            DATERANGE GENERATED ALWAYS AS
                        (daterange(check_in, check_out, '[)')) STORED,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT reservation_room_dates_valid
        CHECK (check_out > check_in),
    CONSTRAINT room_allocation_no_overlap
        EXCLUDE USING gist (
            room_id WITH =,
            stay WITH &&
        )
        WHERE (allocation_state IN ('held', 'confirmed', 'occupied'))
);

CREATE TABLE payments (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    reservation_id  BIGINT NOT NULL REFERENCES reservations(id)
                        ON UPDATE RESTRICT ON DELETE RESTRICT,
    amount          NUMERIC(12, 2) NOT NULL CHECK (amount > 0),
    payment_type    TEXT NOT NULL
                        CHECK (payment_type IN ('charge', 'refund')),
    payment_method  TEXT,
    external_ref    TEXT,
    paid_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    note            TEXT
);

CREATE INDEX reservation_rooms_reservation_idx
    ON reservation_rooms (reservation_id);

CREATE INDEX reservation_rooms_room_dates_idx
    ON reservation_rooms (room_id, check_in, check_out);

CREATE INDEX reservations_status_created_idx
    ON reservations (status, created_at DESC);

CREATE INDEX payments_reservation_paid_idx
    ON payments (reservation_id, paid_at DESC);

其中，stay 是数据库生成的日期范围列，不由应用直接传值。daterange(check_in, check_out, '[)') 创建左闭右开的日期区间；&& 是 PostgreSQL 范围重叠运算符，在 HTML 中必须写成 && 才能正确显示。排斥约束会拒绝同一房间、区间相交且分配状态为 held、confirmed 或 occupied 的记录。

排斥约束不是普通查询条件，而是数据库层并发保护：两个请求即使同时尝试预订同一房间的重叠日期，也不能都成功。应用仍要处理约束冲突并向用户返回可理解的信息。约束只检查数据库中写入的数据，不会替业务自动选房，也不会判断房间是否处于维修状态。

五、状态字段各自表达什么

示例有两个状态字段：reservations.status 表示整张预订单的流程状态；reservation_rooms.allocation_state 表示某个房间分配当前是否占用可售日期。这两者有关联，但不能随意当成同一个字段使用。

例如，一张两间房的订单可能在办理入住时只完成了一间。订单级状态未必足以表达每间房的实际进度。另一方面，为了让排斥约束快速判断某条房间分配是否占用日期，分配表需要有自己的状态。代价是状态存在重复表达的可能，因此应用必须用事务维护一致性，或通过更严格的权限、存储过程和审计规则限制更新入口。

取消一间房时，应将该分配改为 released，以释放区间约束。正常离店后可将其改为 completed。这些状态不会因为日期已经过去而自动改变，需要由业务流程明确更新。若系统规定“订单取消后所有房间自动释放”，取消订单与释放对应分配应在同一个数据库事务中完成，不能先取消订单、后续再异步释放却不处理失败。

示例允许 held 分配参与冲突检查，表示临时保留也占用库存。实际业务要明确保留期限，例如未付款订单保留 10 分钟。数据库不会自动在 10 分钟后改状态；可由定时任务扫描过期保留并更新为 released，同时确保扫描与支付确认之间有明确的并发规则。不要只在排期或应用缓存中假定房间已释放。

六、插入样例数据并查询房态

以下 SQL 可以在上述表创建成功后执行。查询中使用日期字面量作为示例；实际系统应将日期作为参数传入，而不是通过字符串拼接构造 SQL。

INSERT INTO room_types (code, name, max_occupancy, base_rate)
VALUES ('STD', '标准间', 2, 399.00);

INSERT INTO rooms (room_number, room_type_id, floor_number)
SELECT '301', id, 3
FROM room_types
WHERE code = 'STD';

INSERT INTO reservations
    (reservation_code, contact_name, contact_phone, status)
VALUES
    ('R20250801001', '李女士', '13800000000', 'confirmed');

INSERT INTO reservation_rooms
    (reservation_id, room_id, check_in, check_out,
     allocation_state, nightly_rate)
SELECT
    r.id,
    rm.id,
    DATE '2025-08-01',
    DATE '2025-08-04',
    'confirmed',
    399.00
FROM reservations AS r
JOIN rooms AS rm ON rm.room_number = '301'
WHERE r.reservation_code = 'R20250801001';

SELECT
    rm.room_number,
    rt.name AS room_type,
    rr.check_in,
    rr.check_out,
    rr.allocation_state,
    r.reservation_code,
    r.contact_name
FROM reservation_rooms AS rr
JOIN rooms AS rm ON rm.id = rr.room_id
JOIN room_types AS rt ON rt.id = rm.room_type_id
JOIN reservations AS r ON r.id = rr.reservation_id
WHERE DATE '2025-08-02' >= rr.check_in
  AND DATE '2025-08-02' < rr.check_out
  AND rr.allocation_state IN ('held', 'confirmed', 'occupied')
ORDER BY rm.room_number;

上面的房态查询以“某一天是否落在入住区间内”为条件。要查询一段时间内有冲突的分配，可以写成范围重叠判断：

SELECT
    rm.room_number,
    rr.check_in,
    rr.check_out,
    r.reservation_code
FROM reservation_rooms AS rr
JOIN rooms AS rm ON rm.id = rr.room_id
JOIN reservations AS r ON r.id = rr.reservation_id
WHERE rr.room_id = 1
  AND rr.stay && daterange(DATE '2025-08-03',
                           DATE '2025-08-06', '[)')
  AND rr.allocation_state IN ('held', 'confirmed', 'occupied');

注意：查询参数示例中的 room_id = 1 只是示范值，不保证在每个数据库中存在。身份列通常由数据库生成，应用应从前一步查询结果中取得实际 ID，或使用业务编号检索。

七、事务与并发：不要把“先查再插”当作锁

常见的错误流程是：应用先查询某房间在目标日期是否空闲，发现没有冲突，然后插入预订。两个并发请求可能都在对方写入之前完成查询，最终都认为房间可用。单靠应用中的“先查空房”无法可靠处理并发。

数据库排斥约束负责阻止重叠分配落库；应用要把预订单与房间分配放在同一事务中，并处理插入失败。概念上的事务流程如下：

BEGIN;

INSERT INTO reservations
    (reservation_code, contact_name, status)
VALUES
    ('R20250801002', '王先生', 'confirmed')
RETURNING id;

-- 应用取得上一步返回的 id，再尝试插入房间分配。
-- 若触发 room_allocation_no_overlap，回滚整个事务。

INSERT INTO reservation_rooms
    (reservation_id, room_id, check_in, check_out,
     allocation_state, nightly_rate)
VALUES
    (2, 1, DATE '2025-08-03', DATE '2025-08-06',
     'confirmed', 399.00);

COMMIT;

示例使用固定 ID 仅为展示事务结构，真实应用必须绑定参数并使用数据库返回的主键。若第二条插入发生冲突，应执行 ROLLBACK，不能留下只有主表而没有房间分配的半成品订单。数据库连接池和框架通常提供事务 API，实际项目应使用对应 API，而不是把这段文本机械拼接执行。

在 PostgreSQL 中，约束违反会使当前事务进入失败状态，后续语句不能直接继续执行，直到回滚到保存点或回滚整个事务。应用应识别数据库错误并转换为明确的“房间已被占用”业务响应；具体错误码和异常映射应依据 PostgreSQL 驱动与框架文档实现。

八、房型、价格与历史记录

rooms.room_type_id 表示房间当前归属的房型，适合描述房间的结构属性。reservation_rooms.nightly_rate 保存该次分配的每晚成交价，是价格快照；不能在查询历史订单时简单拿当前 room_types.base_rate 代替成交价，因为基础价格日后可能调整。

示例为精简起见只保存一个固定的每晚价格。如果价格会按日期变化、节假日变化或促销拆分，应建立按日期的房价明细或订单价格明细。金额使用 NUMERIC(12, 2) 而非浮点类型，以避免二进制浮点表示造成的舍入误差。是否只允许两位小数取决于币种和业务规则；涉及多币种时，还需明确记录币种代码与舍入规则。

payments 将收款和退款分别作为记录，用 payment_type 区分，金额字段要求为正数。订单实收金额可以根据流水汇总：收款计正、退款计负。不要在每次收款后只覆盖订单上的一个 paid_amount，否则难以追踪部分支付、退款和对账差异。如果为了列表查询维护汇总字段，应把它视为可校验的派生数据，并保证更新流程可靠。

九、房间状态与维修安排的边界

rooms.room_state 表示房间的长期或当前行政状态，例如启用、停用、退役；它不含日期。房间维修通常具有开始和结束时间，不能仅用一个 out_of_service 字符串完整表达历史维修安排。

如果要管理维修时间段，可以增加 room_blocks 表，记录房间、起止日期、原因和状态，并对该表自身增加区间排斥约束，防止同一房间出现相互冲突的维修记录。但 PostgreSQL 普通约束不能直接跨表比较维修区间与预订区间。因此，要防止“维修期间仍然可订”，需要在事务中统一检查与写入，或通过更完整的库存设计、受控存储过程等方式管理这类跨表规则。只在前端隐藏按钮不是数据完整性保证。

同理，维护房间清洁状态、脏房状态或当天可售状态时，应让字段含义清楚。房间“启用”不等于今天干净，也不等于未来日期空闲。不同维度不要塞进一个名为 status 的字段中。

十、安全与参数使用

查询预订、插入房间分配时，应使用数据库驱动支持的参数绑定，例如把房间 ID、日期和联系人作为参数传给 SQL。不要将用户输入直接拼接进 SQL 字符串；参数化查询用于区分 SQL 结构与数据，可防止输入被当作 SQL 语句执行，但它不替代权限控制、输入校验或业务规则验证。

CHECK、外键和排斥约束可以限制数据库层面的非法关系或值，但不限制某个用户能查看哪些客人信息。数据库账号应遵循最小权限原则：应用运行账号只获得业务所需的读写权限，不宜使用超级用户。手机号等个人信息应按实际需要收集、限制访问并制定保留期限；示例没有实现加密、脱敏、审计或隐私合规流程，不能把建表完成误认为已满足全部安全要求。

示例中的 TEXT 没有声明最大字符长度，数据库不会据此自动保证电话号码格式正确。若业务有长度、格式或国家区号要求，应在应用层进行校验，并考虑是否需要数据库约束；不要把未经确认的格式规则硬编码为通用规则。数据库连接应使用受保护的凭据，并通过安全配置管理，避免把密码写入代码仓库。

十一、常见错误清单

• 把房间状态当作日历库存：“空闲”无法表示某个未来日期段是否被占用，应以分配记录及日期区间判断。
• 使用字符串保存多间房：如把“301,302”写入一个字段，会使外键、查询和并发保护都难以落实。
• 将离店日也算作占用：若区间端点规则不统一，连续订单容易被误判冲突。应明确使用左闭右开，所有查询保持同一约定。
• 只在应用层检查冲突：并发请求可能同时通过检查。需要数据库约束或具有明确并发控制的库存机制。
• 直接删除已发生业务的房间或订单：删除会破坏历史关系。示例对关键外键使用 ON DELETE RESTRICT，通常应停用或取消，而不是物理删除。
• 订单取消后不释放房间：状态和区间仍占用库存。应在同一事务中完成订单取消及分配释放。
• 用当前房型价计算历史订单：价格变更后历史金额会失真，应保存成交价或价格明细快照。
• 把订单联系人等同于入住人：代订、多人入住时会丢失实际入住人信息，需要额外的入住人关系设计。
• 把“已付款”写成无法核对的单一字段：部分付款与退款应保留流水，并明确净收款的计算规则。

十二、上线前的检查要点

正式投入使用前，至少确认以下事项：系统是否允许一单多房；房间是预订时确定还是入住前才分配；临时保留是否占库存、何时过期；取消、未到店和提前离店如何释放区间；维修是否需要阻止预订；价格是否按日期拆分；哪些人可以查看客人个人信息。

还应为常用查询建立合适索引，并用真实数据分布检查执行计划。示例索引便于按订单、房间和状态查询，但不代表对所有业务都最优。外键负责引用完整性，索引负责访问效率，二者用途不同。新增约束或扩展到已有数据库时，应先评估数据清理、锁表和迁移时长，并准备可回滚的发布方案。

归根结底，客房管理表结构的核心不是给每张表多加几个状态字段，而是把“谁订了什么、具体占用哪间房、占用哪段日期、记录何时释放”清晰表达出来。先明确业务边界，再用外键、日期区间、事务和适当约束保护数据，后续扩展房价、入住人、维修与对账时，系统才有稳定的基础。