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

On Fri, 20 May 2011 09:48:45 -0500,
Jack Christensen <jackc@hylesanderson.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

On Fri, May 20, 2011 at 08:41:06AM -0500, 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.

It could be that your analogy there is a little too simple (library
management systems' circulation modules are often incredibly
complicated, because of the different classes of restriction on
circulation). Nevertheless, there is a system built atop Postgres or
SQLite: http://biblioteq.sourceforge.net/index.html. I'm unable to
find the license, though the web page says it's "open source".

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 5/20/2011 10:15 AM, Seb wrote:

On Fri, 20 May 2011 09:48:45 -0500,
Jack Christensen<jackc@hylesanderson.edu> wrote:

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.

In a similar project I worked on start time for a loan was always the
current time so overlaps weren't an issue. I don't have any firsthand
experience with them, but it sounds like what you want are exclusion
constraints.

http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/

--
Jack Christensen
jackc@hylesanderson.edu

On Fri, 20 May 2011 12:08:34 -0400,
Andrew Sullivan <ajs@crankycanuck.ca> wrote:

[...]

It could be that your analogy there is a little too simple (library
management systems' circulation modules are often incredibly
complicated, because of the different classes of restriction on
circulation). Nevertheless, there is a system built atop Postgres or
SQLite: http://biblioteq.sourceforge.net/index.html. I'm unable to
find the license, though the web page says it's "open source".

Thanks, they do provide the sql schema in postgresql, so this is very
helpful.

Thanks,

--
Seb

On Fri, 20 May 2011 11:37:36 -0500,
Jack Christensen <jackc@hylesanderson.edu> wrote:

[...]

In a similar project I worked on start time for a loan was always the
current time so overlaps weren't an issue. I don't have any firsthand
experience with them, but it sounds like what you want are exclusion
constraints.

http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/

This lead to a more general solution presented nicely by Jeff Davis:

http://pgfoundry.org/projects/temporal
http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints

Cheers,

--
Seb