Обсуждение: implementing check-in/check-out of an items table

Поиск
Список
Период
Сортировка

implementing check-in/check-out of an items table

От
Seb
Дата:
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

Re: implementing check-in/check-out of an items table

От
Jack Christensen
Дата:
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


Re: implementing check-in/check-out of an items table

От
Seb
Дата:
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

Re: implementing check-in/check-out of an items table

От
Andrew Sullivan
Дата:
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

Re: implementing check-in/check-out of an items table

От
Jack Christensen
Дата:
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


Re: implementing check-in/check-out of an items table

От
Seb
Дата:
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

Re: implementing check-in/check-out of an items table

От
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