Обсуждение: Need help with constraint to prevent overlaps

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?

I know I could do this in the python application code I am using on top
of postgres, but I really like putting as many constraints as possible
in my data model.

TIA

Matt

>
> 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).