Hello, I'm making a booking app with Cake and I'd like to get some help on how to query free time slots _efficiently_ to schedule a booking. I have the following database tables (which can be modified, if needed):
CREATE TABLE IF NOT EXISTS `dbname`.`reservation` ( `id` INT NOT NULL AUTO_INCREMENT , `time` DATETIME NOT NULL , `user_id` INT NOT NULL , `employee_id` INT NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB CREATE TABLE IF NOT EXISTS `dbname`.`reservation_service` ( `id` INT NOT NULL AUTO_INCREMENT , `reservation_id` INT NOT NULL , `service_id` INT NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB CREATE TABLE IF NOT EXISTS `dbname`.`service` ( `id` INT NOT NULL AUTO_INCREMENT , `name` INT NOT NULL , `duration` INT NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB The relations are HABTM between services and reservations (reservation_service is the join table). One reservation can include multiple services. So reservation table contains the datetime field `time` for the start time of the reservation and end time can be calculated by finding the services for the reservation and adding the minutes to the reservation start time from each service `duration` field. Now I need to make following queries: 1. Query next free time slot for certain amount of minutes 2. Same as above, but just from certain employee_id(s) (I guess it will be as simple as adding a condition to the query, but thought to mention if it makes any difference) 3. Query all free time slots for certain amount of minutes for certain time span (for example all free time slots for this week when making a booking calendar) 4. Same as above, but just from certain employee_id(s) I think it would be simpler to query these, if I make it so that the reservation table also has the end time for the reservation and fill it up when reservation is added by adding the minutes from each chosen service. So consider `end_time` datetime field to be in reservation table if it helps. Also I'm not sure how the free time slots can be queried when the reservations table contains only booked times, but if you know an efficient solution for this, please write back. I will be very grateful for any help, Thanks! --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
