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(at)hylesanderson(dot)edu