预约系统设计-DB部分

最近看了一部分陈天老师在B站发的预约系统设计视频，觉得讲的挺好的。特别是DB部分，使用pg实现起来的真的非常简单，所以写文章记录一下。

当然也推荐大家去看原视频， Rust 项目实操 - 从零开始构建预定系统（1）：思考需求，构建 RFC，评论里也有他的仓库地址。

需求

有两种角色，学生和老师。老师可以发布自己可预约的时间，学生预约。（大概就么个意思）

表设计

例子中没有为表添加普通索引

-- 预约记录表 
CREATE TABLE reservation_history ( 
    id serial, 
    student_id int not null, 
    reservation_id int not null, 
    during tsrange, 
    
    CONSTRAINT reservations_history_pkey PRIMARY KEY (id), 
    CONSTRAINT reservations_history_conflict EXCLUDE USING gist (reservation_id WITH =, during WITH &&) 
); 
 
-- 教师可预约时间表 
CREATE TABLE reservation ( 
    id serial, 
    teacher_id int not null, 
    during tsrange NOT NULL, 
    
    CONSTRAINT reservations_pkey PRIMARY KEY (id), 
    CONSTRAINT reservations_conflict EXCLUDE USING gist (teacher_id WITH =, during WITH &&) 
);

主要解释两个地方，tsrange 类型 和 gist 索引

文档

www.postgresql.org/docs/curren… www.postgresql.org/docs/curren…

范围类型是代表某种元素类型（称为范围的子类型）的数值范围的数据类型。在我们这个例子中，可预约时间就正好是一段范围。pg 还针对范围类型提供了很多实用的方法，具体可以看文档。

相比于用两个字段表示，比如 start_time, end_time。用一个字段表示，配合pg提供的内置函数，实现更加简单和简洁。

对于可预约时间，我们不想要一个唯一的约束，而是需要一个不重叠的约束，此时就需要gist索引了。

教师发布可预约时间

直接插入即可

INSERT INTO reservation(teacher_id, during) VALUES (1108, '[2022-11-01 14:30, 2022-11-01 16:30)');

如果可预约时间重复，则会报错

INSERT INTO reservation(teacher_id, during) VALUES (1108, '[2022-11-01 14:30, 2022-11-01 17:30)');
ERROR:  conflicting key value violates exclusion constraint "reservations_conflict"
DETAIL:  Key (teacher_id, during)=(1108, ["2022-11-01 14:30:00","2022-11-01 16:30:00")) conflicts with existing key (teacher_id, during)=(1108, ["2022-11-01 14:30:00","2022-11-01 15:30:00")).

学生预约

begin;
-- 直接插入，如果时间有重叠则直接报错
INSERT INTO reservation_history(student_id, reservation_id, during) VALUES (?, ?, '[2022-11-01 14:30, 2022-11-01 17:30)');

-- 检查学生预约的时间是否包含在老师可预约时间里，主要使用`Overlaps`函数，
select tsrange('[2022-11-01 14:30, 2022-11-01 17:30)') && select during from reservation where id = ?;
-- 如果是false，则rollback
-- 这里还有一个坑需要注意，如果是预约范围包围可预约范围的话，也会返回true
-- 这时候最好再判断下边界条件
-- select (select during from reservation where id = ?) @> (select lower(tsrange('[2022-11-01 14:30, 2022-11-01 17:30)')));


-- 或者直接判断上下边界
-- 可预约边界大于预约的最大边界
-- select (select during from reservation where id = ?) @> (select upper(tsrange('[2022-11-01 14:30, 2022-11-01 17:30)')));
-- 可预约边界小于预约的最小边界
-- select (select during from reservation where id = ?) @< (select lower(tsrange('[2022-11-01 14:30, 2022-11-01 17:30)')));
-- 同时为true才可以commit，否则rollback
commit;

实际写代码的时候，不需要用事务，先读出来，判断预约时间是否合理，在代码里判断代替用sql判断，实现简单的多。再直接插入就好了。

常见问题

在使用docker创建的pg容器里，创建表失败

在pg命令里运行

CREATE EXTENSION btree_gist;

具体看此链接

总结

用pg的range类型可以很简单的实现类似的预约功能，而且mysql是不支持的。