We have a Slot table to store appointments. The app receives the date and multiple slots which are then divided into smaller slots of 15 mins interval.
CREATE TABLE `Slot` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `starttime` datetime NOT NULL, `endtime` datetime NOT NULL, `assigned_To_Id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `appointment_assigned_to_id_foreign` (`assigned_To_Id`), CONSTRAINT `appointment_assigned_to_id_foreign` FOREIGN KEY (`assigned_To_Id`) REFERENCES `Employee` (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1
Now, the admins can add slot for a employee(assigned_To_Id) for a particular day by specifying the slots : e.g. :
7 July Slots : 11:45-12:15 13:00-13:30 and slot time = 15 mins (above time range will be divided in slot time intervals of 15 mins and assigned_To_Id = 1
so Slots table will look like :
|id| startime | endtime | assigned_To_Id | |1 | 2018-07-07 11:45:00 | 2018-07-07 12:00:00 | 1 | |2 | 2018-07-07 12:00:00 | 2018-07-07 12:15:00 | 1 | |3 | 2018-07-07 13:00:00 | 2018-07-07 13:15:00 | 1 | |4 | 2018-07-07 13:15:00 | 2018-07-07 13:30:00 | 1 |
Now, I need to summarise this in the front end : like
7 July Slots : 11:45-12:15 13:00-13:30
Should I store these 2 high level slots in a different table or should I generate them from the Slots table ? How can I go about writing a query to fetch 2 high level slots from the Slots table ?
Edit : Slots in the table can be 'x' minutes (any value decided by the admin but it will remain fix for a given period of time). When bigger slots are broken into smaller slots - we check that these smaller slots do not already exist in db (so no overlaps) link overlap question here
Requests will come in multiple of 'x' mins.
(so take big slots as inputs, divide them into smaller slots and when later the Vendor revisits the app - show the big slots that he defined). I need to know how to manage the big slots (store in separate table, store in same table with a flag or write a query on smaller slots to generate summarize slots)