I'm doing a major Year 12 IT assignment, and my teacher can't find a solution.
I really need one within a few days, because we have to start our projects soon
and they'll be due within two weeks.

I'm creating a database for a client - a restaurant - and part of it
is a table booking system. I have a table called Reservations:

- TABLENO
- DATE
- TIME
etc

My problem lies in the fact that:

- same table cannot be booked at the same time on the same night

But the following can be booked:

- same table, same time, different date
- same table, same date, different time (most likely at set intervals from a drop-down list - e.g. at 30 min intervals, say)
- multiple tables, same date, same time

I originally thought of creating primary keys, or at least
preventing duplicate data entry, for each field. However, I realised
this was not practical as it would prevent the combinations listed
above from occurring (I think - I'm not incredibly experienced on all
this and find setting up table relationships most challenging of all!)

If you could help me, I'd be most grateful. Ideally the solution
would still only involve using one table, but if it really had to
have more than one table, I could cope!

Also (ideally) the solution wouldn't involve SQL or VB (because I
don't understand it much) but if it had to, I'd also cope.

If you can, please email your response directly to me at grace_adair@yahoo.co.uk. Many thanks, Grace.

declare a compound primary key

CREATE TABLE tablereservation
( tableno varchar(3)
, resdate, datetime
, primary key (tableno,resdate)
)

note: resdate is a DATETIME value, containing both date and time

Hi,

I am assuming that a reservation (booking) for a table can be made for a specified table, on a specified date for a specified period of time. This can be done as follows:

Using a single table reservation (table_num, booking_start_time, booking_end_time) with the primary key being (table_num, booking_start_time)

You will have to write code in a trigger that ensures that for a table, for a booking on a given date, the booking time periods do not overlap. One way to check for overlap is by using analytic functions lead or lag.

A sample query for checking overlaps could be:

SELECT * FROM (
Select table_num, booking_start_time, booking_end_time,
lead(booking_start_time, 1) over (
partition by table_num order by booking_start_time) as
next_start_time
from reservation
)
WHERE booking_end_time > next_start_time

This query returns overlapped times. The trigger should not permit any rows that create overlapped data.

Hope that helps

Ravi

do you happen to know which databases support the analytic syntax?

p.s. hello Toronto

Oracle 8i onwards supports analytic functions.

Where analytic functions are not available, it may be possible to write a User Defined Function which performs the same task. On large volumes of data, analytic functions will probably outperform User Defined Functions.

I do not know of any other database that supports analytic functions. I do not know much about other databases except a bit about PostgreSQL. I also know enough about MYSQL to realize that it is not a database worthy of consideration for any serious work. [Hope I have not offended anybody :-) ]

Analytic and Data Mining tools such as SAS ought to have some version of these functions. Don't know enough about them to say for sure.


Ravi

Hi,

I've been thinking about SQL and ranges and overlaps. It would be nice if SQL would let you define a "RANGE" composite data type that can be used with any "primitive" datatype (more precisely "system defined" datatype).

In addition, if an "Overlap" function were defined, then it would make life a lot easier for developers.

For example, something like the following declarative pseudo-syntax could be used:

Create table t1 (
id NUMBER,
RANGE (date) (start_time, end_time) AS time_range,
NO OVERLAP (time_range over ID),
primary key (id, start_time)
)

This states that there is a time_range consisting of two "date" columns named start_time and end_time. It specifies that, for all rows with a given ID, the time_range should not overlap.

This is declarative. A database engine may enforce it any way it wants.

Of course, a call to the system catalog for information about the table should show the "range" fields and the "no overlap" constraint.

The benefit is that this keeps the spirit of SQL in that it is Declarative (you declare what needs to be done, not how to do it.)

The other benefit is that developers and designers do not have to write any code for this. This is exactly how declarative referential integrity works! So it is not such a radical idea.

Well, just some thoughts I had on hoping that the database vendors will make my life easier!

Ravi

well, i am certainly not offended, i did not write mysql

nevertheless, i know plenty about it, and about other databases as well, and i personally think mysql is great

i'd give mysql a 9 out of 10 on the satisfaction index, and my next favourite one comes in at 6 out of 10

let me repeat, on my satisfaction index

as the saying goes, "your mileage may vary"

and if perhaps by "serious" you meant "multi-million-dollar IT budget and a building full of security and performance engineers" then i would say the choices boil down to only three -- microsoft, sybase, mysql, and oracle

did i say three? i meant five

hey, we both were writing at the same time, and you beat me!

you said "In addition, if an "Overlap" function were defined..."

there is, it's in the sql standard

it's pretty slick, too

however, do any of the big five support it?

hey, i'll bet mimer does, if anybody does

yup, i was right: The OVERLAPS predicate

I did a quick search of the mimer website looking for the word overlap. There were no matches.

As for the SQL Overlaps predicate, I was thinking of something more powerful and something that could be used just like "references" of referential integrity. Also, the "range" and overlaps definition should be useable for any data type, not just dates. At this point, I do not know where other datatypes could be used, but, if needed, could prove very valuable.

The current SQL overlaps predicate asks you to specify two date ranges (or expressions that evaluate to date ranges) and so may or may not be useable declaratively in a "create" or "alter" DDL syntax.

That is not good enough for me. Like Oliver Twist, I want more! I want the database vendors to do everything possible to make my life easier.