Temporal nested sets with boundary overlap

Hi, I'm designing the database for a project with seasonal pricing, in the
example the hotel rooms have their price defined within a season or if no
season is defined a default price.
Seasons might be defined within seasons, for example in the summer period a
seasonal price might be defined, additionally for all weekends in summer a
weekend price might be defined. Last night I realized this is actually some
kind of nested set model with a twist, the twist is that the boundaries can
overlap: the end date for a weekend price may be the same as the end date
for the whole season.

Please excuse my English, it's not my native tongue, let me clarify this
with some DDL

CREATE TABLE Hotels (
HotelID int IDENTITY,
HotelName varchar(50),
PRIMARY KEY(HotelID)
)

CREATE TABLE RoomTypes (
RoomTypeID int IDENTITY,
RoomTypeName varchar(50),
PRIMARY KEY(RoomTypeName)
)

CREATE TABLE Rooms (
RoomID int IDENTITY,
HotelID int,
RoomTypeID int,
RoomPrice decimal(10,2),
PRIMARY KEY(HotelID, RoomTypeID)
)
CREATE TABLE Seasons (
SeasonID int IDENTITY,
RoomID int,
DateFrom DATETIME,
DateTo DATETIME,
RoomPrice decimal(10,2),
)
-- insert one hotel
INSERT INTO Hotels(HotelName) VALUES('Nested inn')
-- insert 4 types of rooms
INSERT INTO RoomTypes(RoomTypeName) VALUES('Single Room')
INSERT INTO RoomTypes(RoomTypeName) VALUES('Double Room')
INSERT INTO RoomTypes(RoomTypeName) VALUES('Family Room')
INSERT INTO RoomTypes(RoomTypeName) VALUES('Honeymoon Suite')
-- associate each roomtype with the hotel
INSERT INTO Rooms(HotelID, RoomTypeID, RoomPrice) VALUES(1,1,10)
INSERT INTO Rooms(HotelID, RoomTypeID, RoomPrice) VALUES(1,2,20)
INSERT INTO Rooms(HotelID, RoomTypeID, RoomPrice) VALUES(1,3,30)
INSERT INTO Rooms(HotelID, RoomTypeID, RoomPrice) VALUES(1,4,40)
-- define price for whole year
INSERT INTO Seasons(RoomID, DateFrom, DateTo, RoomPrice)
VALUES(1,'2008-01-01', '2008-12-31', 11)
-- define seasonal price for summer
INSERT INTO Seasons(RoomID, DateFrom, DateTo, RoomPrice)
VALUES(1,'2008-06-01', '2008-08-31', 12)
-- define seasonal price for last weekend of summer
INSERT INTO Seasons(RoomID, DateFrom, DateTo, RoomPrice)
VALUES(1,'2008-08-30', '2008-08-31', 13)


The SQL I've come up with to select the price for a room on a certain date

SELECT
Rooms.RoomID,
Rooms.RoomTypeID,
Seasons.SeasonID,
RoomTypes.RoomTypeName,
Seasons.DateFrom,
Seasons.DateTo,
Seasons.RoomPrice
FROM Rooms
INNER JOIN RoomTypes ON
Rooms.RoomTypeID = RoomTypes.RoomTypeID
INNER JOIN
Seasons ON
Rooms.RoomID = Seasons.RoomID
LEFT JOIN
Seasons AS Seasons2 ON
Seasons.RoomID = Seasons2.RoomID
AND (
Seasons.DateFrom < Seasons2.DateFrom
AND Seasons.DateTo = Seasons2.DateTo
OR
Seasons.DateFrom = Seasons2.DateFrom
AND Seasons.DateTo > Seasons2.DateTo
OR
Seasons.DateFrom < Seasons2.DateFrom
AND Seasons.DateTo > Seasons2.DateTo
)
WHERE
Seasons2.RoomID IS NULL
AND HotelID = 1
AND '2008-08-30' BETWEEN Seasons.DateFrom AND Seasons.DateTo

And to select the default price from table Rooms in case no seasons have
been defined I'd add

UNION ALL

SELECT
Rooms.RoomID,
Rooms.RoomTypeID,
NULL,
RoomTypes.RoomTypeName,
CAST('2000-01-01' AS DATETIME),
CAST('3000-12-31' AS DATETIME),
Rooms.RoomPrice
FROM Rooms
INNER JOIN RoomTypes ON
Rooms.RoomTypeID = RoomTypes.RoomTypeID
LEFT JOIN
Seasons ON
Rooms.RoomID = Seasons.RoomID
WHERE
Seasons.RoomID IS NULL
AND HotelID = 1

Now on to some questions:
- what improvements would you suggest to my DDL and DML
- what other models would you suggest for storing for such data
- is there a more efficient way to query this model using mysql compatible
syntax
- is there a more efficient way to query this model using proprietary syntax
- do you see any unhandled edge cases; did I overlook something?

Thanks

Joris van Lier

The DDL is full of common errors.

1) You have IDENTITY and no real keys-- this is RDBMS and we don't
keep a count of the physical insertion attempts in the tables to mimic
record numbering in a sequential file.

2) Aren't hotels identified by a Michelin code or some other industry
standard? I don't know, but then I did not have to do the research,
like you do.

3) There is no such thing as a "_type_id" because an attribute is
either a type (like "blood_type")or an identifier (like VIN on an
automobile) but never both. Are all blood types unique entities? No,
a type is a way of encoding a scalar value that occurs in many
entities. An identifer points to one and only one entity.

4) Since the set of room type is small and not likely to change, you
can put it in a CHECK constraint.

5) Why do you have sooooo many NULL-able columns? Major payroll
systems do have have as many as you have. For example, you allow a
room to exist without know what hotel it is in. Very confusing when
you want to check into the hotel ..

6) We don't say "room_id"; the common term in "room number" and your
key for locating a room was wrong. A hotel could have rooms with the
same room numbers!

7) You have the room prices in two places -- the normal rate with the
Rooms and the seasonal rate. The naming convention is to have
"_" where is
"_", so we need to tell them apart.

CREATE TABLE Hotels
(hotel_id INTEGER NOT NULL PRIMARY KEY, -- find industry standard!!
hotel_name VARCHAR(50) NOT NULL);

CREATE TABLE Rooms
(hotel_id INTEGER NOT NULL,
room_nbr INTEGER NOT NULL,
PRIMARY KEY (hotel_id, room_nbr),
room_type CHAR(12) DEFAULT 'Single' NOT NULL
CHECK(room_type IN ('Single', 'Double', 'Family', 'Honeymoon'))
normal_room_price DECIMAL(10,2) NOT NULL);

7) Apparently, the seasonal stuff applies to **individual** rooms,
which leads to this design:

CREATE TABLE Seasons
(season_name CHAR(15) NOT NULL PRIMARY KEY,
hotel_id INTEGER NOT NULL,
room_nbr INTEGER NOT NULL,
FOREIGN KEY (hotel_id, room_nbr)
REFERENCES Rooms(hotel_id, room_nbr),
season_start_date DATETIME NOT NULL,
season_end_date DATETIME NOT NULL,
CHECK (season_start_date < season_end_date),
season_room_price DECIMAL(10,2) NOT NULL);

I don't have time for the query right now, but it might be something
like this skeleton

SELECT .., COALESCE (S.season_room_price, R.normal.room_price) AS
room_price
FROM Rooms AS R
LEFT OUTER JOIN
Seasons AS S
ON R.hotel_id = S.hotel_id
AND R.room_nbr = S.room_nbr
AND checkin_date BETWEEN season_start_date AND
season_end_date
..
WHERE .. ;

An alternative to your query can be the following using the ranking
functions in SQL Server 2005/2008:

SELECT R.RoomID,
R.RoomTypeID,
S.SeasonID,
T.RoomTypeName,
S.DateFrom,
S.DateTo,
S.RoomPrice
FROM Rooms AS R
INNER JOIN RoomTypes AS T
ON R.RoomTypeID = T.RoomTypeID
INNER JOIN (SELECT RoomID,
SeasonID,
DateFrom,
DateTo,
RoomPrice,
ROW_NUMBER() OVER(
PARTITION BY RoomID
ORDER BY DATEDIFF(DAY, DateFrom, DateTo))
AS seq
FROM Seasons
WHERE '2008-08-30' BETWEEN DateFrom
AND DateTo) AS S
ON R.RoomID = S.RoomID
AND S.seq = 1
WHERE HotelID = 1;

--
Plamen Ratchev
http://www.SQLStudio.com

"--CELKO--" wrote in message
news:3033e189-7a5b-4739-afd5-af4d200cde41-at-k7g2000hsd.googlegroups.com...
> The DDL is full of common errors.

Thanks for pointing these out:v

> 1) You have IDENTITY and no real keys-- this is RDBMS and we don't
> keep a count of the physical insertion attempts in the tables to mimic
> record numbering in a sequential file.

So why do most entry level database tutorials use this numerical key concept
to relate records, isn't that better than joining on char or varchar value?


> 2) Aren't hotels identified by a Michelin code or some other industry
> standard? I don't know, but then I did not have to do the research,
> like you do.

Yes they are, and I'm interfacing with multiple providers webservices each
providing their own identifier

> 3) There is no such thing as a "_type_id" because an attribute is
> either a type (like "blood_type")or an identifier (like VIN on an
> automobile) but never both. Are all blood types unique entities? No,
> a type is a way of encoding a scalar value that occurs in many
> entities. An identifer points to one and only one entity.

But when this identifier has multiple possible keys each to be provided to a
specific system, shouldn't I just create a lookup table for hotel ->
external_system_hotel_key?

> 4) Since the set of room type is small and not likely to change, you
> can put it in a CHECK constraint.

I agree my Sample data was too limited, but actually there's a lot of
RoomTypes and they may change , I don't fancy having to update my check
constraint each time some provider creates a new room type

> 7) You have the room prices in two places -- the normal rate with the
> Rooms and the seasonal rate. The naming convention is to have
> "_" where is
> "_", so we need to tell them apart.

What's the rationale behind the qualifier _underscore_ qualifier naming
convention instead of CamelCase?
I think I'll stick with camel casing, this works well with the .NET front
end code-generation, and according to ISO 11179-5 page 9 it's allowed in my
language
http://standards.iso.org/ittf/Public...-5_2005(E).zip

"Some languages, such as German and Dutch, commonly join grammatical
constructs together in a single
word (resulting in one word which in English or French might be a phrase
consisting of nouns and adjectives).
These languages could use a separator that is not a break between words,
such as a hyphen, space or
underscore, but instead capitalize the first letter of each name part within
a single word (sometimes called
CamelCase). This naming convention is also commonly used in programming
languages such as C++ and
Java."

> 7) Apparently, the seasonal stuff applies to **individual** rooms,
> which leads to this design:

No that's a misunderstanding, I'm not storing individual rooms but the types
of rooms available from a hotel
I'm revising the schema to include providers and per-provider keys:

CREATE TABLE hotels (
hotel_id INTEGER NOT NULL PRIMARY KEY, -- our primary lookup key
hotel_name VARCHAR(50) NOT NULL
);

CREATE TABLE providers (
provider_id INTEGER NOT NULL PRIMARY KEY,
provider_name VARCHAR(50) NOT NULL
);

CREATE TABLE provider_hotels (
hotel_id INTEGER NOT NULL,
FOREIGN KEY(hotel_id) REFERENCES hotels(hotel_id),
provider_id INTEGER NOT NULL,
FOREIGN KEY(provider_id) REFERENCES providers(provider_id),
UNIQUE KEY (hotel_id, provider_id),
provider_hotel_key VARCHAR(50) NOT NULL -- per provider lookup key,
UNIQUE KEY (provider_id, provider_hotel_key)
);

CREATE TABLE hotel_room_types (
hotel_id INTEGER NOT NULL,
room_type_id INTEGER NOT NULL,
PRIMARY KEY (hotel_id, room_type_id),
normal_room_price DECIMAL(10,2) NOT NULL
);

CREATE TABLE provider_seasons (
provider_id INTEGER NOT NULL,
FOREIGN KEY (hotel_id, room_type_id)
hotel_id INTEGER NOT NULL,
room_type_id INTEGER NOT NULL,
FOREIGN KEY (hotel_id, room_type_id)
REFERENCES hotel_room_types(hotel_id, room_type_id),
season_start_date DATETIME NOT NULL,
season_end_date DATETIME NOT NULL,
CHECK (season_start_date < season_end_date),
season_room_price DECIMAL(10,2) NOT NULL
);



> I don't have time for the query right now, but it might be something
> like this skeleton
>
> SELECT .., COALESCE (S.season_room_price, R.normal.room_price) AS
> room_price
> FROM Rooms AS R
> LEFT OUTER JOIN
> Seasons AS S
> ON R.hotel_id = S.hotel_id
> AND R.room_nbr = S.room_nbr
> AND checkin_date BETWEEN season_start_date AND
> season_end_date
> ..
> WHERE .. ;

I'll try and work on that example, however the customers requirements are
quite volatile in this stage,
I'm now contemplating how to create a schema to store a price for each day
of the week within a season

CREATE TABLE provider_seasons (
provider_id INTEGER NOT NULL,
FOREIGN KEY (hotel_id, room_type_id)
hotel_id INTEGER NOT NULL,
room_type_id INTEGER NOT NULL,
FOREIGN KEY (hotel_id, room_type_id)
REFERENCES hotel_room_types(hotel_id, room_type_id),
season_start_date DATETIME NOT NULL,
season_end_date DATETIME NOT NULL,
CHECK (season_start_date < season_end_date),
season_room_price_monday DECIMAL(10,2) NOT NULL,
season_room_price_tuesday DECIMAL(10,2) NOT NULL,
season_room_price_wednesday DECIMAL(10,2) NOT NULL,
season_room_price_thursday DECIMAL(10,2) NOT NULL,
season_room_price_friday DECIMAL(10,2) NOT NULL,
season_room_price_saturday DECIMAL(10,2) NOT NULL,
season_room_price_sunday DECIMAL(10,2) NOT NULL
-- I'm not sure about defining the primary key here
);

OR

CREATE TABLE provider_seasons (
season_id INTEGER NOT NULL PRIMARY KEY,
provider_id INTEGER NOT NULL,
FOREIGN KEY (hotel_id, room_type_id)
hotel_id INTEGER NOT NULL,
room_type_id INTEGER NOT NULL,
FOREIGN KEY (hotel_id, room_type_id)
REFERENCES hotel_room_types(hotel_id, room_type_id),
season_start_date DATETIME NOT NULL,
season_end_date DATETIME NOT NULL,
CHECK (season_start_date < season_end_date),
UNIQUE KEY (provider_id , hotel_id, room_type_id, season_start_date,
season_end_date)
);

CREATE TABLE provider_season_daily_prices (
season_id INTEGER NOT NULL,
FOREIGN KEY (season_id)
REFERENCES seasons(season_id),
day_of_week INTEGER NOT NULL,
CHECK(day_of_week >= 1 AND day_of_week <= 7),
UNIQUE KEY (season_id, day_of_week)
);

>> So why do most entry level database tutorials use this numerical key concept to relate records [sic], isn't that better than joining on char or VARCHAR(n) value? <<

If you are really worried about the speed of joins, then don't use SQL
at all. A file system with pointer chains will be MUCH faster than
SQL; this is why there is still more data in IMS than in all the SQL
products on mainframes. The purpose of SQL and the RDBMS is data
integrity, data portability, data security and flexibility.

Since each auto-increment vendor extension is different, which
tutorials are you read? In a *real* book on RDBMS, the concept of a
key and references get a good discussion. A key must be a subset of
attributes in the table. That is the definition; then there are some
desirable properties -- validation, verification, familiarity, etc.

>> Yes they [hotels] are, and I'm interfacing with multiple providers web services each providing their own identifier <<

I would pick one to use and then convert the others to it. This drives
me nuts; you would think that a well-established industry would have
agreed on things by now. I keep running into this same problem in
securities in the international markets where ISIN is just starting to
make progress. Bu then I live in a country that has not gone Metric
yet...

>> But when this identifier has multiple possible keys each to be provided to a specific system, shouldn't I just create a lookup table for hotel -> external_system_hotel_key? <<

Identifiers don't have keys; identifiers are keys. Yes, you will need
an auxiliary translation table, but you should pick one and only one
of the standard alternatives for your database. Do not invent one on
your own.

>> I agree my Sample data was too limited, but actually there's a lot of RoomTypes and they may change , I don't fancy having to update my check constraint each time some provider creates a new room type <<

Agreed. I don't know the hotel industry; does this happen a lot? I
assumed that each chain would have a limited set of offerings that
remain fairly constant over decades.

>> What's the rationale behind the qualifier _underscore_ qualifier naming convention instead of CamelCase? <<

This is an ISO convention from ISO-11179. The data element names use
letters, digits and underscore only (no special characters), so they
will port across what we used to called the X3J languages (ANSI X3J
was the programming languages Standards group).

Even Microsoft gave up on camelCase. If you read a Latin-based
language, your eye jumps to the uppercase letters; they start
sentences or mark proper nouns. The underscore does not cause that eye
movement; my theory is that we got used to reading lined paper in
school. All uppercased words form a Bouma, so the underscore breaks
them into easily read units -- these days that is holdover from punch
cards and mainframes.

>> No that's a misunderstanding, I'm not storing individual rooms but the types of rooms available from a hotel I'm revising the schema to include providers and per-provider keys: .. however the customers requirements are quite volatile [LOL! and will probably stay that way forever!] in this stage, I'm now contemplating how to create a schema to store a price for each day of the week within a season <<

Go one step further: Why not a price per hotel, per room type, per
calendar day? That would let you handle odd holidays that break the
seasonal pattern, do special promotional discounts, etc.? If this is
under 1 million rows, it looks tempting.