1

I want to store in MySQL a series of simple (never infinite) repeating appointments.

Example an appointment starts at date/time 21 JUL 2020 0900 -> end date/time 25 JUL 2020 0930. This equates to 5 appointments :

21 JUL 0900-0930
22 JUL 0900-0930
23 JUL 0900-0930
24 JUL 0900-0930
25 JUL 0900-0930

I do not think storing it the way I have outlined: with start/end dates and times is correct.

Similar question here makes me think I should structure it like this table:

ID    event_id    meta_key        metavalue
1        1        event_start     1596445200  - 9AM MON 3 AUG 2020
2        1        event_duratn    1800 (int?) - 30 minutes
3        1        event_interval  86400 (int?)- 24 hours
4        1        event_repeat    3 (int?)    - repeat 3 times
5        2        event_start     1596475200  - 9AM MON 10 AUG 2020
6        2        event_duratn    1800        - 30 minutes
7        2        event_interval  86400       - 24 hours
8        2        event_repeat    0           - do not repeat, only do it once
9        3        next event.......

Or like this:

ID   event_id event_start  event_duratn  event_interval   event_repeat
 1      5      1596445200      1800         86400              3
 2      3      1596475200      1800         172800             2

Which is the correct way to store this sort of repeating temporal data?

Al Grant
  • 2,102
  • 1
  • 26
  • 49
ian d
  • 45
  • 3
  • You could have your table with fields: Appointment, startDate, endDate, and appointmentTime, which would give you one record for 5 appointments. – Ethan Aug 02 '20 at 19:08
  • If an event has repetitions, you have to store them individually with start and end date. Everything else will be impossible to query by index but always require computation. The invariant meta data for a repeated event (pattern, name) belong in a separate table. Potentially varying meta data belongs in a 3rd table with n:1 on metadata and 0..1:1 on instance so you can have both per-series defaults and per-instance specializations. – Ext3h Aug 04 '20 at 09:32
  • The first proposal is EAV schema design, which sucks. Don't do it. – Rick James Aug 09 '20 at 03:50

1 Answers1

0

You can create a table "Appointment_Slots" with columns "Appointment_Slot_Name", "Start_Time" and "End_Time".

Ex table:

Appointment_Slot_Name   |  Start_Time   |  End_Time
AP1                     |   09:00       |  09:30
AP2                     |   09:30       |  10:00
AP3                     |   10:00       |  10:30

Now create your data table "Appointments" with columns "Appointment_Slot_Name", "Date" and any other columns as per your requirement. If this is not the answer you are looking for, please reply.

GMB
  • 216,147
  • 25
  • 84
  • 135
MALNEEDI VAMSI
  • 36
  • 5
  • So ultimately the data is stored with a entry for each instance of the appointment on a separate table? – ian d Aug 02 '20 at 20:54
  • Since they are sequential, and is a simple repeating pattern, why not a start date, start time, duration, and interval. – ian d Aug 03 '20 at 00:35