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@xxxxxxxxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
