MySQL architecture question regarding null columns vs. joins

Question

Hello,

I have an application where I'll have repeating events. So an event can repeat by day, "every n days", by week, "every n weeks on Mon/Tue/Wed/etc", and by month, "every n months on the 1st,2nd,3rd,etc".

What is the best way to handle this from a table design perspective? I can think of two ways but I'm not sure which one is better.

1) 5 columns for the above, 1 for the day case and 2 each for week and month. Whichever ones are not being used would be null. In my application I could see the nulls and choose to ignore them.

2) Have a second table, say events_dateinfo or something, against which I'd JOIN for the query.

Seems like option 2 is probably more 'normalized' and what not, but does it strike you as overkill for such a simple thing? Also, if I were to go option 2, is there a way to translate rows into columns - that is, select the 2 week attributes for a specific event and have them treated as columns?

Thank you

Answer 1

I would prefer to handle this normallized, The events in one table, and the event recurrency in another.

Handling the indexes in a appropriate way, you can handle the request for data through views, or if data gets larger, as an audit table with triggers.

Answer 2

If I understood right event can have more than 1 schedule (this is why you want " to translate rows into columns ").

You will need not 2 but 3 tables in this case; third one must be junction table. You can easily add new schedules if you need in the future with this scheme. So, something like this:

table events (event_id, event_name, description)
table schedules (sch_id, schedule)
table event_schedule (event_id, sch_id)

There isn't PIVOT possibility in MySQL as I know, but you can use GROUP_CONCAT() function in SELECT; it'll be one row per event and all schedules for one event will be in one column.

SELECT e.event_name AS Event, GROUP_CONCAT( s.schedule SEPARATOR ', ') AS Schedule
         FROM events e 
    (LEFT) JOIN event_schedule es
    ON e.event_id = es.event_id
    JOIN schedules s
    ON s.sch_id = es. sch_id
         GROUP BY e.event_name;