Mysql（+ php）存储，检索和显示日期范围的正确方法

Question

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.

Answer 1

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