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

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.

CREATE TABLE items(
   item_id SERIAL PRIMARY KEY,
   ...
);

CREATE TABLE loans(
   loan_id SERIAL,
   item_id integer NOT NULL REFERENCES items,
   start_time timestamptz NOT NULL,
   end_time timestamptz
   ...
);

CREATE UNIQUE INDEX ON loans(item_id) WHERE end_time IS NULL;

--
Jack Christensen
jackc@hylesanderson.edu