构建一个酒店客房预订系统，核心目标是准确记录房间、客户以及两者之间的预订关系，并在任何时刻都能快速回答“今天哪些房能卖”。SQLite以单个文件存储全部数据，支持外键、事务和触发器，足以支撑几十间客房规模的前台日常运营，且免安装、易备份。
一、数据库设计与建表
在动手写代码前，先理清业务实体。酒店里有“房间”“客户”和“预订”三个主体。房间有编号、类型、价格和状态；客户有姓名、手机和证件号；预订则关联客户与房间，并记录入住、离店时间和支付金额。如果把这些字段全塞进一张表，不仅冗余，还会在客户多次预订时反复拷贝手机号，更新也容易漏。
因此采用三张表，通过外键约束保证引用完整。下面语句在SQLite中开启外键支持并建表，注意SQLite默认不强制外键，需要在连接后执行PRAGMA foreign_keys=ON。
PRAGMA foreign_keys=ON;
CREATE TABLE room (
room_id INTEGER PRIMARY KEY,
room_no TEXT NOT NULL UNIQUE,
room_type TEXT NOT NULL,
price REAL NOT NULL,
status TEXT NOT NULL DEFAULT 'available'
);
CREATE TABLE customer (
cust_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
phone TEXT,
id_card TEXT
);
CREATE TABLE booking (
book_id INTEGER PRIMARY KEY,
cust_id INTEGER NOT NULL,
room_id INTEGER NOT NULL,
check_in DATE NOT NULL,
check_out DATE NOT NULL,
amount REAL NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (cust_id) REFERENCES customer(cust_id),
FOREIGN KEY (room_id) REFERENCES room(room_id)
);
CREATE INDEX idx_booking_date ON booking(check_in, check_out);
上面的room_no加了唯一约束，避免两个房间用同一个门牌号。booking表上的联合索引能加速“按日期区间查预订”的语句。SQLite的索引采用B树，对范围查询友好，但也会稍微拖慢写入，客房量少时完全可以接受。
设计上把房间状态字段留在room表里，但实际房态更多是由booking推导出来的。我们保留它是为了前台一眼看到空闲房，后续可用触发器同步，而不是每次都联表算。
二、防止重复预订的事务处理
最怕的场景是两位前台同时点开同一间空房并确认入住，结果系统写出两条重叠的预订。SQLite的事务隔离级别是可串行化，配合立即加锁能挡住这类冲突。写操作时用BEGIN IMMEDIATE先拿写锁，再查再插，最后提交。
下面Python片段演示一次安全入住：先锁库，查目标日期该房是否已被占，没有才插入预订并把房间标为已住。任何一步异常都回滚，房间状态不会被改脏。
import sqlite3
def check_in(db_path, cust_id, room_id, cin, cout, amount):
conn = sqlite3.connect(db_path)
try:
conn.execute('PRAGMA foreign_keys=ON')
conn.execute('BEGIN IMMEDIATE')
cur = conn.execute(
'SELECT 1 FROM booking WHERE room_id=? AND check_in < ? AND check_out > ?',
(room_id, cout, cin)
)
if cur.fetchone():
raise Exception('该房间在选定日期已被预订')
conn.execute(
'INSERT INTO booking(cust_id,room_id,check_in,check_out,amount) VALUES(?,?,?,?,?)',
(cust_id, room_id, cin, cout, amount)
)
conn.execute('UPDATE room SET status='occupied' WHERE room_id=?', (room_id,))
conn.commit()
except Exception as e:
conn.rollback()
raise e
finally:
conn.close()
这种写法把“查重”和“写入”放在同一个事务里，中间不会被别的连接插入数据。SQLite在单文件下用文件锁实现，多进程前台共用同一db文件时也能工作。缺点是写期间别的写操作会阻塞，但酒店前台并发写入很少，体验无影响。
若将来要上Web多实例，可以把db放网络盘或换PostgreSQL；当前结构改动很小，只需把连接串换掉，SQL几乎不变，这也是起步用标准SQL的好处。
三、用触发器自动释放过期预订
客户预订了没来且过了入住日，房间应自动回到可售池。手动跑脚本容易忘，用SQLite触发器在插入后校验太重，更轻的做法是每天开门前执行一条清理，或写AFTER DELETE触发器配合取消操作。这里给出基于日期查询释放的存储过程式脚本，逻辑清晰且可定时跑。
下面的SQL把“离店日小于今天且房间被标占”的房间重置为空闲。放在晨会前由计划任务调用，避免前台人工改状态。
UPDATE room
SET status='available'
WHERE room_id IN (
SELECT r.room_id
FROM room r
LEFT JOIN booking b ON r.room_id=b.room_id
WHERE r.status='occupied'
AND (b.check_out < DATE('now') OR b.book_id IS NULL)
);
触发器适合做强制规则，比如禁止离店早于入住：
CREATE TRIGGER chk_date BEFORE INSERT ON booking
BEGIN
SELECT CASE
WHEN NEW.check_out <= NEW.check_in THEN
RAISE(ABORT, '离店日期必须晚于入住日期')
END;
END;
这样在应用层忘了校验时，数据库仍会抛错，保护底层数据合理。SQLite的RAISE函数能在触发器里中断语句，是兜底手段。
四、产出某日可售房清单
店长常问“五一当天还剩几间大床房”。用左连接把房间和当天预订比对，没命中就是可售。下面查询返回指定日期未被占的房间及价格。
SELECT r.room_no, r.room_type, r.price FROM room r LEFT JOIN booking b ON r.room_id=b.room_id AND b.check_in <= '2024-05-01' AND b.check_out > '2024-05-01' WHERE b.book_id IS NULL AND r.room_type='大床房' ORDER BY r.room_no;
该语句利用LEFT JOIN保留全部房间，再用b.book_id IS NULL过滤掉有重叠预订的房。日期条件写在ON里而非WHERE，否则会先把预订删掉再连，语义错。索引idx_booking_date让区间判断走树查找，几十到几百行数据毫秒出结果。
如果加上客户联表，还能顺带导出“今日续住名单”，把booking里check_in早于今日且check_out晚于今日的记录拉出来即可，结构完全一致。
五、备份与日常维护
SQLite单文件优势在备份：直接拷走hotel.db就全量落盘。建议写个脚本每日落盘到另一块盘，并用VACUUM整理碎片。
cp hotel.db /backup/hotel_$(date +%F).db sqlite3 hotel.db 'VACUUM;'
长时间删除预订会产生空页，VACUUM重建文件缩小体积，也顺带校验结构。对于客房数不过百的店，整个db常驻内存都无压力，查询延迟忽略不计。
当出现可疑写入失败时，用PRAGMA integrity_check跑一遍，SQLite会报页损坏或记录越界，比肉眼查表可靠。结合前面事务与触发器，小酒店系统能稳跑多年不需重构。