Hi,
I am trying to figure out how to set up `reservations` database:
So far I have this:
Code:
CREATE TABLE `reservations` (
`reservation_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`nanny_id_FK` int(11),
`customer_id_FK` int(11),
`from` datetime,
`to` datetime,
`status` tinyint(1) NOT NULL
COMMENT '1-ordered by customer, 2-confirmed by nanny,
3-canceled by customer, 4-canceled by nanny,
5-executed by nanny', 6-nanny was paid',
INDEX `from`,
INDEX `to`,
INDEX `nanny_id_FK`,
INDEX `customer_id_FK`
);
Lets say customer fills out a reservation form where he wants to hire(reserve)
"Anna"(nanny_id_FK=3) on May 16, 2008 from 7pm - 9pm.
Form is submitted and sql is made to find out if that time slot is free.
Lets say "Anna"(nanny_id_FK=3) is already taken from
May 16, 2008 6pm to
May 17, 2008 8am.
Bear in mind that "Anna" might already have more jobs lined up(saved in database)
e.g May 20, 2008 8pm to 11pm.
As we can see above "Anna" is already taken => customer gets a message back saying that
"Anna" is not available at requested time slot.
My questions:
1) what should db schema look like?
2) what would sql query look like to tell customer
whether "Anna" is available or not at required time slot?
My rough attempt:
Code:
SELECT * from reservations WHERE nanny_id_FK=3 AND
(from is between '2008-05-16 19:00:00' AND '2008-05-16 21:00:00') OR
(to is between '2008-05-16 19:00:00' AND '2008-05-16 21:00:00');
Dev Shed Forums Sponsor:
May 2nd, 2008, 02:18 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
