FAQ
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,

--
Seb

Search Discussions

  • Jack Christensen at May 20, 2011 at 2:48 pm

    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
  • Seb at May 20, 2011 at 3:16 pm

    On Fri, 20 May 2011 09:48:45 -0500, Jack Christensen 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
  • Jack Christensen at May 20, 2011 at 4:37 pm

    On 5/20/2011 10:15 AM, Seb wrote:
    On Fri, 20 May 2011 09:48:45 -0500,
    Jack Christensenwrote:
    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
  • Seb at May 20, 2011 at 7:00 pm

    On Fri, 20 May 2011 11:37:36 -0500, Jack Christensen 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
  • Andrew Sullivan at May 20, 2011 at 4:08 pm

    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
  • Seb at May 20, 2011 at 5:50 pm

    On Fri, 20 May 2011 12:08:34 -0400, Andrew Sullivan 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 20, '11 at 1:41p
activeMay 20, '11 at 7:00p
posts7
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase