A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all properties
that are available for a given period.
What I want to be able to say is show me all homes that are available between
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon
> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
[vbcol=seagreen]
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)
|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.
|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP
|||Hi everyone
[vbcol=seagreen]
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes things
faster but I don't really care. I just do it for clarity and to avoid conflicts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
[vbcol=seagreen]
Declarative Referential Integrity, data types, etc. in your schema are. Sample
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible columns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidified
it in my mind.
Thanks again
Simon