Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to Query Min PK Value NOT in Table

Re: How to Query Min PK Value NOT in Table

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 22 Jun 2005 12:34:41 -0400
Message-ID: <35ednQW2Qti5DiTfRVn-pA@comcast.com>

"simon" <simon_at_simon.com> wrote in message news:42b98a66$1_1_at_rain.i-cable.com...
>
>> The simple answer is that filling in missing numbers is an irrelevancy
>> serving no useful purpose to anyone or anything: So don't. A surrogate
>> key must be unique. There is no business requirement anywhere that they
>> be sequential from the standpoint of filling them in after the fact.
>>
> Perhaps let me rephase it with a real life example (this is imaginary, it
> may not be a good example, but i'm just trying to illustrate the need):
>
> 1. A table representing seat reservation of a cinema (perhaps let's assume
> the schema is existing and cannot be changed):
>
> CREATE TABLE reservation (
> PK NUMBER primary key,
> seat_no NUMBER unique,
> blah_blah_blah....
> );
> 2. Also, let's allow the PK to have no business meaning and will be
> generated from a sequence. So, the PK in my original post should be
> changed
> to some other unique key, say, seat_no, that represents a number
> corresponds
> to seat in the cinema. This number is not arbitrary and must within to a
> certain range.
>
> 3. Each row in the table represents a reservation for a seat_no. If the
> reservation is cancelled, the row is deleted or moved into another
> reservation log table. No matter how, in the existing schema, each row
> existing in the table must represent an existing reservation and any
> cancelled booking must mean the row deleted from this table.
>
> 4. As a result, seat_no can be of this sequence:
>
> 1, 2, 5, 8, ...
>
> if previous reservation for seat_no 3, 6, 7 have been cancelled.
>
> 5. For group reservation, the system has to automatically allocate a set
> of
> adjacent seat_no's from the bottom or from a user-selected range. For
> example, to allocate 2 empty seats between seat_no 2,8 means we need to
> search the table in order to know 3,6,7 are the available seat_no between
> 2,8.
>
> And, this is why I need to search for non-existing values - my business
> requirement is a bit different but hopes this illustrate why there could
> be
> a need to SELECT value not in the table.
>
>
>

in your scenario, your non-existent values should exist in another table: SEAT (a master list of seats)

you also need an EVENT table

your RESERVATION table then becomes an associative table that allows each SEAT to be booked once for each EVENT. if the RESERVATION is cancelled, the RESERVATION row is deleted -- the SEAT and the EVENT still exist.

you will then find available seats and blocks of seats by an outer-join on SEAT and RESERVATION (for a specific EVENT ID), likely with a GROUP BY and COUNT to determine number or adjacent seats

you may want to google this newsgroup for previous recommendations on data modeling books

++mcs Received on Wed Jun 22 2005 - 11:34:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US