0

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)

  • Should I store these 2 high level slots in a different table or should I generate them from the Slots table? Both variants are possible. First variant contains the possibility of a logical contradiction. Second variant is more expencive. a query to fetch 2 high level slots from the Slots table If your MySQL is v.8+, use recursive CTE. If not, I recommend to use stored procedure, not query. – Akina Jul 11 '18 at 12:03
  • thanks @Akina. Can you elaborate on "First variant contains the possibility of a logical contradiction" – j10 Jul 11 '18 at 12:48
  • Nothing prevents contradiction of data in tables. Does the constraints which checks that the data in one tables matches the data on another table and vice versa exists? I think no... – Akina Jul 11 '18 at 13:34
  • Yes, it can get difficult to sync and maintain data consistency in both tables. How about if we use the same table and use a flag like "isLongInterval" and set it to true for big slots and false for 15 mins interval slot. How does this approach sound ? – j10 Jul 12 '18 at 4:43
  • Think to the direction of add all timeslots for a day (with NULL values in the assigned_To_Id field) if any timeslot of this day is created/inserted. Or maybe create a monthly timeslots pack beforehand. To compact the timeslots into timeperiods by continuous not-null values range is easy. – Akina Jul 12 '18 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.