Postgresql is enough (for a room reservation system)

Of course PostgreSQL is a enough for a room reservation system.

But, motivated by seeing yet another “room reservation system replaced by a worse room reservation system” situation in the wild, I just wanted to find out how much work is it build a simple room reservation system in PostgreSQL and friends.

Much has been written on this naturally, here are some links:

• PostgreSQL documentation
• Just use Posgres!
• PostgreSQL is Enough
• https://blog.danielclayton.co.uk/posts/overlapping-data-postgres-exclusion-constraints/
• https://www.slideshare.net/slideshow/not-just-unique-exclusion-constraints/5356101

Framework

I based this on the very nice Mathesar spread-sheet like UI for Postgresql. I have it running it a systemd managed “quadlet” on my system, so PosgreSQL admin and UI are solved straight away.

Data model

The complete data model is easily built in Mathesar using the UI

Two tables:

The rooms table, with UNIQUE constraint on “room” name:

This is the UNIQUE constraint setup:

The reservations table with foreign key linking the “room” column:

That is the basic data model done. Only remaining item is to add an exclusivity constraint so that double-booking is prohibited at the database layer, thereby making complex client logic unnecessary:

CREATE EXTENSION btree_gist;

ALTER TABLE reservations
   ADD CONSTRAINT no_overlapping_reservations 
   EXCLUDE USING gist ( room WITH =, tsrange(rstart, rend) WITH && );

Data entry form

Mathesar makes it trivial to create a data entry form, which can be access controlled etc :

Overall a very low effort to build a multi-user capable basic reservation system!