1

I'm working on a calendar application where you can set events which can last multiple days. On a given date there can be multiple events, already started or starting that day. The user will view everything as a calendar.

Now, my question is: which of the following is the right approach? Is there an even better approach?

1) use 2 tables, events and events_days and store the days of every event in the event_days table

2) use just one table with the events stored with a date_from field and a date_to field and generate every time with mysql and php a list of days for the desired range with the events

Obviously the first option will require much more db storage, while the second one will require a bigger work from the server to generate the list (every single time the user asks for it).

The db storage shouldn't be a problem for now, but i don't know if will be the same in the future. And i fear the second option will need too many resources.

dgtal
  • 193
  • 16

1 Answers1

2

I have used both approaches. Here is a list of pros and cons that I have noticed:

Two tables: events(id) and events_dates(eventid, date)

Pros:

  • Query to check if there are events on a given date or between given dates is trivial:
SELECT eventid FROM events_dates WHERE date BETWEEN '2015-01-01' AND '2015-01-10'
  • Query to select the list of dates for an event is trivial
SELECT date FROM events_dates WHERE eventid = 1

Cons:

  • While selecting the list of dates is trivial, inserting the list of dates for an event requires scripting (or a table-of-dates)
  • Additional measures required to make sure data remains consistent, for example, when inserting an event spanning three days you need four insert queries
  • This structure not suitable in situations where time is involved, for example, meetings schedule

One table: events(id, start, end)

Cons:

  • Query to check if there is are events on a given date or between given dates is tricky.
  • Query to select the list of dates for an event is tricky.

Pros:

  • Inserting an event is trivial
  • This structure suitable in situations where time is involved
Community
  • 1
  • 1
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Ok. First of all thanks for the answer. I agree with you. Now, the problem is, you say if time is involved the second one will be better. But what if the starting and ending time will be needed ONLY when an event lasts one day and NEVER in multiple day events? – dgtal Jan 19 '15 at 14:11
  • Let me give you a example: if for example you are creating an availability calendar for something that can be booked many times in a single day then first approach might not work (e.g. you cannot check if that thing is available on 2015-01-01 between 1PM and 5PM). If time is involved, I would choose the second approach for consistency, even if only 1% of records use time. – Salman A Jan 19 '15 at 14:22