CODEWITHSUNDEEP
phpmysqldatabasedatetimedatabase-design
dgtal
dgtal

Reputation: 303

Mysql (+php) Right approach to store, retrieve and show date ranges

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.

Upvotes: 1

Views: 93

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272106

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

Upvotes: 2

Related Questions