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 proper
ties
that are available for a given period.
What I want to be able to say is show me all homes that are available betwee
n
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 thing
s
faster but I don't really care. I just do it for clarity and to avoid confli
cts.
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. Samp
le
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 colum
ns.
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 solidifi
ed
it in my mind.
Thanks again
Simon