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));
>> 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. <<
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