Re: diary constraints

Hi folks

I know this has been discussed in the past, but no amount of keywords
has returned anything from the archives.

I want to create a courtesy car diary diary system where I have a table
containing all of the cortesy cars in the pool, and then an allocation
table which has two timestamps, one for the start date/time and one for
the return date/time.

How do I go about creating constraints on inserts and updates to ensure
that

a) the finish is after the start
b) two allocations for a single vehicle don't overlap.
--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000

On Aug 23, 2005, at 5:33 PM, Gary Stainburn wrote:

> I want to create a courtesy car diary diary system where I have a
> table
> containing all of the cortesy cars in the pool, and then an allocation
> table which has two timestamps, one for the start date/time and one
> for
> the return date/time.
>
> How do I go about creating constraints on inserts and updates to
> ensure
> that
>
> a) the finish is after the start
> b) two allocations for a single vehicle don't overlap.

This is an interesting problem. You might want to take a look at this
book, previously mentioned on the one of the lists (by George Essig,
I believe):

> Developing Time-Oriented Database Applications in SQL
> by Richard T. Snodgrass
>
> The book is out of print, but the author has made the PDF available
> on his website at:
> http://www.cs.arizona.edu/people/rts/tdbbook.pdf

Hope this helps!

Michael Glaesemann
grzm myrealbox com

On 8/23/05, Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> wrote:
>
> Hi folks
>
> I know this has been discussed in the past, but no amount of keywords
> has returned anything from the archives.
>
> I want to create a courtesy car diary diary system where I have a table
> containing all of the cortesy cars in the pool, and then an allocation
> table which has two timestamps, one for the start date/time and one for
> the return date/time.
>
> How do I go about creating constraints on inserts and updates to ensure
> that
>
> a) the finish is after the start

check constraint like -> "finish_time > start_time" this will do

b) two allocations for a single vehicle don't overlap.

Use "overlaps" function in a trigger to validate the above.

--
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

> a) the finish is after the start

well, finish > start

> b) two allocations for a single vehicle don't overlap.

this one is a bit tricky !

- Check that there is no allocation in the table whose time period start,
end includes either the start of the end of the reservation to insert, and
that the time period of the reservation to insert does not contain either
the start or end of any reservation in the table.

This is 4 selects, playing with order by limit 1 and indexes, it will be
fast.

am 23.08.2005, um 9:33:58 +0100 mailte Gary Stainburn folgendes:
> Hi folks
>
> I know this has been discussed in the past, but no amount of keywords
> has returned anything from the archives.
>
> I want to create a courtesy car diary diary system where I have a table
> containing all of the cortesy cars in the pool, and then an allocation
> table which has two timestamps, one for the start date/time and one for
> the return date/time.
>
> How do I go about creating constraints on inserts and updates to ensure
> that
>
> a) the finish is after the start

with a check-constraint like this:
create table foobar (t1 timestamp, t2 timestamp check (t2>t1));

> b) two allocations for a single vehicle don't overlap.

possibly with a trigger.

Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===