Let’s say a PostgreSQL database has three tables, users, entities and connections, where the latter is a join table to keep track of which users are connected to which entities (so it has the expected user_id and entity_id columns).
Users can have multiple (past) connections to an entity, and I want to keep track of these, but they should only be allowed to have one active connection. By "active" I mean that the current time is between connections.valid_from and connections.valid_to (or something similar - if there is a better way to model this, I’m all ears). It would also be preferable if overlapping periods were not allowed for any user/entity combination.
I could of course ensure that these constraints are met at the application level, but I would much prefer to do it at the database level.
My understanding is that using now() is impossible in a unique index, so I’m guessing I would need something more advanced, like a trigger?
What is the best way to model something like this?
Instead of from/to, use a single tsrange column. Ranges have a lot of built in operators which make checking for overlap easy, like &&. Then add an exclusion constraint on any overlapping timestamp ranges.
CREATE TABLE connections (
...
valid_during tsrange not null,
EXCLUDE USING GIST (valid_during WITH &&)
);