As I am building an appointments app I have created a table that holds all possible appointments slots and if I want to get the available appointments I just left loin this table with another one that holds the already booked appointments....
http://forums.mysql.com/read.php?125,636504
Nonetheless in getting the available appointments I must add another criteria also..that of the schedules of business providers...if a hair salon opens at 10:00 for example there is not point showing to the user 09:00 as an available time-slot.
I hold the schedules in this table:
CREATE TABLE `store_open` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`b_user_ID` mediumint(11) unsigned DEFAULT NULL,
`open_time` time DEFAULT NULL,
`close_time` time DEFAULT NULL,
`open_time_b` time DEFAULT NULL,
`close_time_b` time DEFAULT NULL,
`day` varchar(12) DEFAULT NULL,
`model_ID` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `b_user_ID` (`b_user_ID`),
CONSTRAINT `busers_reference` FOREIGN KEY (`b_user_ID`) REFERENCES `business_users` (`crID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
where you see _b it refers to a different session in the day where the store is open.
For example it opens in the morning,closes at noon and opens again in the afternoon(session b).
But to be honest I am not sure that I can add this criteria the way the table is designed now...probably a redesign of this table is required...
What do you think?Thanks