0

I am creating a booking management system in which it is allowed to create recurrent events.

Searching around, I understood that creating "repeating patters" would be an optimal idea for the DB design, as explained here: Calendar Recurring/Repeating Events - Best Storage Method

My issue comes from the fact that I would need to add some data for each single event, such as if payments have been made for each single event, confirmation, notes, etc.

This would end in creating a different table with a single row for each event created. In other words, physically adding a row for each event instead of using "recurrent patterns".

I can't see a solution for avoiding 1 line in the DB for 1 event. Any suggestion? In my system, each user would not have many events, let's say a maximum of 50 events per week.

J03Bukowski
  • 102
  • 3
  • 19

1 Answers1

0

Let's assume you have a table already to store recurrent events, such as

table recurrent_event 
id bigint
start date
interval int -- as simple or complex as needed

Now your application will need some logic anyway to calculate which single events will come from this, e.g. to display a list of single events. I would not store a list of all these singles events in the database, as, initially, this list wouldn't add any useful information. Also, the list would have to end somewhere, so it might fail to encompass all single events coming from the recurrent event. The only need to insert a record for a single event arises when some additional information for the event gets actually entered. Just for these single events, I'd create a

 table single_event_additional_info
 id bigint
 recurrent_event_id bigint
 single_event_date date
 additional-information ... whatever datatype fitting

that points back to the recurring event. So, when treating a recurring event, selecting from this table all single events referring to it will yield all information relevant for the recurring event. The rest of single events will still be determined by calculation.

TAM
  • 1,731
  • 13
  • 18
  • Thanks for your reply. Would you be functional to add at the "single_event_additional_info" table example a field that point the date of a single event, otherwise the additional information will point the whole recurrent event period, so each single event – J03Bukowski Dec 05 '20 at 11:04