> > I'm building a shift-scheduling app. I want to make a constraint in my > database that prevents one human from being assigned to work two > different jobs at the same time. > > In other words, if I schedule John Doe to mop bathrooms from 10 AM > until > 4 PM, some other manager will not be able to schedule John Doe for a 1 > PM meeting. > > How can I do this with constraints? Would I need to write a trigger > that does some 'select ... between ...' work? > Matt, We do a lot of scheduling work, and the way we handle this is with a stored procedure. The only way to add something to the schedule is by calling the stored procedure. The procedure queries the existing schedule first, using the OVERLAPS function listed here: http://www.postgresql.org/docs/8.1/static/functions-datetime.html The overlaps is very easy and simple to use. If any conflicts are found, we can return information about what's overlapping to the application. You'll have to do some locking as well, so you don't create a race condition between when you check for a conflict and when you commit the new schedule item. (You could probably put the logic into a trigger too, but that would just be throwing away the insert or raising an exception, whereas with the stored procedure, we're returning an actual rowset of details regarding the overlapping schedule item).
