[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] implementing check-in/check-out of an items table
On Fri, 20 May 2011 09:48:45 -0500, Jack Christensen ja...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] implementing check-in/check-out of an items table
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 a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] implementing check-in/check-out of an items table
On 5/20/2011 10:15 AM, Seb wrote: On Fri, 20 May 2011 09:48:45 -0500, Jack Christensenja...@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 ja...@hylesanderson.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] implementing check-in/check-out of an items table
On Fri, 20 May 2011 12:08:34 -0400, Andrew Sullivan a...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] implementing check-in/check-out of an items table
On Fri, 20 May 2011 11:37:36 -0500, Jack Christensen ja...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
