Обсуждение: implementing check-in/check-out of an items table
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
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