Stairway to Database Design Level 4: Building a Schema

  • Joe Celko

    SSCertifiable

    Points: 5578

    Comments posted to this topic are about the item Stairway to Database Design Level 4: Building a Schema

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • Tom Wickerath

    Valued Member

    Points: 51

    SQL Server 2017 (CU18)

    I cannot get Joe Celko's example to work, as advertised!

    create table Hotel
    (room_nbr integer not null,
    arrival_date date not null,
    departure_date date not null,
    guest_name char(30) not null,
    constraint schedule_right
    check (arrival_date <= departure_date)
    );
    go


    create or alter view Valid_Hotel_Stays (room_nbr, arrival_date, departure_date, guest_name)
    as
    select H1.room_nbr, H1.arrival_date, H1.departure_date, H1.guest_name
    from Hotel as H1
    where not exists
    (select *
    from Hotel as H2
    where H1.room_nbr = H2.room_nbr
    and H2.arrival_date < H1.arrival_date
    and H1.arrival_date < H2.departure_date)
    and H1.arrival_date <= H1.departure_date
    with check option;
    go

    insert into Valid_Hotel_Stays values (1, '2011-01-01', '2011-01-03', 'Ron Coe');

    -- Followed by

    insert into Valid_Hotel_Stays values (1, '2011-01-03', '2011-01-05', 'John Doe');

    --will give a WITH CHECK OPTION clause violation on the second INSERT INTO statement, as we wanted.

    Both rows insert without an error.

  • Tom Wickerath

    Valued Member

    Points: 51

    A friend of mine, Microsoft Access MVP Ben Clothier, provided a fix. Change the following line of code from "less than" to "less than or equal to":

    From:  and  H1.arrival_date < H2.departure_date)
    to: and H1.arrival_date <= H2.departure_date)

    In addition, one can use the DateTime data type, instead of Date, to create a more realistic example, where a guest usually checks out by 11:00 AM, and a new guest can check into the same room later in the afternoon. With a Date only datatype, this is not accommodated. Here are my modifications to the sample, which include "or alter" (introduced with SQL Server 2016) for the create view statement:

    create table Hotel
    (room_nbr integer not null,
    arrival_date datetime not null,
    departure_date datetime not null,
    guest_name char(30) not null,
    constraint schedule_right
    check (arrival_date <= departure_date)
    );
    go

    create or alter view Valid_Hotel_Stays (room_nbr, arrival_date, departure_date, guest_name)
    as
    select H1.room_nbr, H1.arrival_date, H1.departure_date, H1.guest_name
    from Hotel as H1
    where not exists
    (select *
    from Hotel as H2
    where H1.room_nbr = H2.room_nbr
    and H2.arrival_date < H1.arrival_date
    and H1.arrival_date <= H2.departure_date)
    and H1.arrival_date <= H1.departure_date
    with check option;
    go

    insert into Valid_Hotel_Stays values (1, '2011-01-01 15:00', '2011-01-03 11:00', 'Ron Coe');
    insert into Valid_Hotel_Stays values (1, '2011-01-03 15:00', '2011-01-05 11:00', 'John Doe');

    insert into Valid_Hotel_Stays values (5, '2019-12-19 15:00', '2019-12-25 11:00', 'Bilbo Baggins');
    insert into Valid_Hotel_Stays values (5, '2019-12-25 15:00', '2019-12-31 15:00', 'BoobooBear');
  • Jeff Moden

    SSC Guru

    Points: 996826

    Just be mindful of the possible surprise such a change could cause if the departure data has a midnight time.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Tom Wickerath

    Valued Member

    Points: 51

    Hi Jeff,

    Care to provide an example INSERT query or two that demonstrates the possible surprise you mention--I assume in this case the possibility of double booking a room?

    Thanks

    • This reply was modified 7 months, 2 weeks ago by  Tom Wickerath.
  • Tom Wickerath

    Valued Member

    Points: 51

    Additional information provided by my friend, Microsoft Access MVP Ben Clothier, via a private email (after I thanked him):

    "You're welcome! To be honest it did puzzle me for a bit; though it seemed a simple change, it took me a while to remember that it was basically a double negation (because we are doing a NOT EXISTS + invalid condition, rather than NOT EXISTS + valid conditions).

    It's probably the first example I've found where CHECK OPTION would actually have a use. I knew about it for long time but never actually used it in production. Futzed with it but found that it created more problems than it solved so just left it off."

    • This reply was modified 7 months, 2 weeks ago by  Tom Wickerath.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply