Re: implementing check-in/check-out of an items table

On Fri, 20 May 2011 09:48:45 -0500,
Jack Christensen <jackc(at)hylesanderson(dot)edu> wrote:

> On 5/20/2011 8:41 AM, Seb wrote:
>> Hi,

>> I'm trying to implementing the checking in and checking out of items
>> in a table, whereby an item cannot be checked out if it's not
>> checked-in. I've searched for schemas for public libraries where
>> this is a key requirement, but haven't managed to hit the right
>> keywords to get relevant results.

>> Thanks,

> Use a loans table with unique partial index to ensure that only one
> unreturned loan per item can exist at a time.

[...]

Thanks, this certainly avoids loaning an item before it's returned, but
it doesn't protect against having loans that overlap in time. For
example, an item can have a start_time that is between start_time and
end_time of a previous loan for that same item. My first thought was to
have some CHECK constraint with a query, but this doesn't seem to be
supported by postgresql.

--
Seb