How to configure an SQL table to efficiently and easily store multiple dates and times for individual events?

advertisements

I have a list of events that I wish to store the starting and ending times for in an SQL database. The catch is that some of the events only take place during one day whereas other events occur across multiple days. Sometimes the events only occur once per day, and others occur multiple times (see examples below for more clarification).

How do I set up my Events table to efficiently store all versions of start and end dates and times?

Example data that needs to be stored in the table:

Event Name                        |    # of Participants    | Times and Dates
==========================================================================================================
Three-legged-race                 |          14             |    08:00 - 09:30 on Mar 23
------------------------------------------------------------------------------------------------------
Pie/Hot Dog Eating Contests       |           9             |    12:00 - 14:00 and
                                  |                         |    15:30 - 17:30 on Mar 23
------------------------------------------------------------------------------------------------------
Decathlon                         |          37             |    10:00 - 14:00 on Mar 23
                                  |                         |    11:00 - 16:00 on Mar 24
------------------------------------------------------------------------------------------------------
Daily Opening/Closing Ceremonies  |         100             |    07:50 - 08:00 and
                                  |                         |    17:30 - 17:40 on Mar 23
                                  |                         |    07:50 - 08:00 and
                                  |                         |    17:30 - 17:40 on Mar 24


As a side note, I'm alright with storing the dates and times as Unix Datetimes if necessary, but I would prefer to store the value individually if necessary (for easier access/isolation/use later on).

Edit:

I forgot to add that i would like the solution to be self contained in a single table. I don't want to use SQL joins or foreign keys to combine multiple tables (like the Events table with an EventsTime table).

Edit #2:

...and I was hoping for only two or so columns (not start_datetime1, end_datetime1, start_datetime2, end_datetime2, etc.).


Create a EventTime table? Which conctains something similar to below (idea):

EventTime (id, event_id, start_datetime, end_datetime)

That way you could have multiple event_id's no issue and you can have it in any format you described (across multiple days, single day etc)