4 ms·

I'd like to know this as well. I think you'll just have to build things (potentially horribly) and fail. I took three semesters of database (granted, baby data

• IvanVergiliev 6y ago
  Exclude constraint on a GiST index? https://stackoverflow.com/a/51247705 https://stackoverflow.com/a/51247705
• sk5t 6y ago
  The documentation for Postgresql range types describes how to do exactly this. https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPES-CONSTRAINT https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPE... Edit: and if you didn't want to use postgres, you could have "starttime" and "endtime" columns and reject any bad bookings with a before insert / before update trigger.
  • gavinray 6y ago
    This approach is the best and works really well if you don't need to do a join on a related table to look up information. If you need to use data outside of the current table for exclusions/check constraints, you have to write a trigger function (as far as I know). I had to solve this recently, where the actual start/end times were stored on a related table. I'm no SQL wizard, but I'd love to share my solution in case it helps others (it might be terrible). Note: I changed the actual tables/domain to be generic, this is a poor example and it made more sense for my usecase, but this shows general approach. -- Let's pretend we have these tables (awful design, but for sake of example): -- room <-> reservation <-> reservation_info -- Where "reservation_info" has "start_time" and "end_time" CREATE FUNCTION check_for_overlapping_reservations() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF ( -- Find the newly created reservation and join it with the info record to grab "start_time" and "end_time" for check below with this_reservation as ( select * from reservation inner join reservation_info on reservation_info.id = reservation.reservation_info_id where reservation.id = NEW.id ), bookings_for_timerange as ( -- Select every other reservation, where the reservation is happening in the same room select * from reservation as other_reservation, this_reservation inner join reservation_info as other_reservation_info on other_reservation_info.id = other_reservation.reservation_info_id where other_reservation.room_id = this_reservation.room_id AND -- And the timerange from start to end overlaps the newly created record tstzrange(this_reservation.start_time, this_reservation.end_time) && tstzrange(other_reservation_info.start_time, other_reservation_info.end_time) -- Get a count of all the records, it should only be 1. If it's greater than one, there's overlap. select count(*) from bookings_for_timerange ) > 1 THEN RAISE EXCEPTION 'Room is already reserved during this time period'; END IF; RETURN NEW; END;$$;
    • sk5t 6y ago
      TBH it's a bit more verbose and less performant than need be, but hey, if it works, rock on! But don't use 'select *' in production code. Consider next time something like if exists (select from sometable t1 join sometable t2 on t1.resource_id = t2.resource_id and t1.res_id <> t2.res_id and tstzrange(t1.start, t1.end) && tstzrange(t2.start, t2.end) where t1.res_id = new.res_id ) then ... raise exception
      • gavinray 6y ago
        Ahh, that looks much cleaner. Thanks for the knowledge =)
• ticmasta 6y ago
  hold start & end time in normal DB columns, use business logic for your rule enforcement, have a query to identify rule violation. Maybe add constraints on your warehouse data but I probably wouldn't on the operational DB. I could be convinced to do so for the PoC because it might make sense, but what if you're managing thousands or millions of scheduled "resources" like a chain of libraries, or hotel rooms? What if you need to coordinate "leases"? DB-level is an OK place to start, but also one of the hardest to tease apart when you need to scale. This is kind of the point of data modeling; you start with an idea, make it all pretty 3rd-normal form, define your projected indices and checks and constraints, then you mess it up a bit where it makes sense or you've had experience in the past. YMMV