Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    80

    Help needed ASAP - primary keys best? Restaurant database.

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Restaurant Database

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you happen to know which databases support the analytic syntax?

    p.s. hello Toronto
    Last edited by r937; 05-11-04 at 20:48.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •