Bookings Table Design

C

cafe

(1) is way better: see
http://support.microsoft.com/support/kb/articles/Q100139.ASP

Here's one way of doing it.

tblTool
ToolID (PK)
Descrition ("24 watt scrumper bumper")
other details of the tool in question

tblBooking
ToolID ( composite )
StartTime ( primary key )
Duration (1 hour, whatever)

To find all tools available on Monday between 12 and 4pm:

SELECT *
FROM tblTool AS t
WHERE NOT EXISTS
( SELECT 1
FROM tblBooking AS b
WHERE b.ToolID = t.ToolID
AND b.StartTime + b.Duration BETWEEN xxx AND yyy )

where xxx and yyy are whatever syntax you need to identify Monday 12pm &
Monday 4pm respectively.

HTH,
TC
(off now for 24 hours)
 
D

diarmuidq

Hi
I'm designing a db for booking tools. These tools can be rented out at
any time, am or pm, and always in hour slices. I'm trying to decide whats
the best method

1) Date of booking, with a start and end time.
or
2) Date of booking, with 24 Yes/No fields. Each Yes/No representing an hour.

Method 2 will make it easier to represent on reports, but then gives the
overhead of extra fields and counting how many hours were booked.

One of the main functions will be check what tool is available in what date.
For example, which heavy duty drills are available on Monday between 12 and
4pm? Any suggestions on table design, or good methods of calculating the
availibility would be great. I'm not looking for code, just ideas.
thanks
Diarmuid
 
Top