I'm trying to figure out the best way to organize data in my mysql database. I have a single entry that will have several items for a single field, for example:
id | event | dates | etc
Using this example, one event might have several dates corresponding to it. My first thought would to just serialize an array with all the dates and place it in the dates field, but as far as I know there isn't a way to order the output of the query by each of those dates. So my other solution would just have another table called something like "event_dates" where it would contain the id of the event and a single date, but I'm worried that might not be the best solution.
So my question is what would be the best way to go about this? Would having a separate table containing individual dates create more overhang than having them all be in one field or is there another solution I just haven't been able to think of?
Serialize data or individual entries suggestion
Moderator: General Moderators
Re: Serialize data or individual entries suggestion
If you have a many-to-many situation, where events can occur on multiple dates and dates can have multiple events, then you should actually have 3 tables.
events {id, name, etc}
dates{id, date}
event_dates{id, event_id, date_id}
It might seem wasteful to use 3 tables for this, but it gives you more flexibility (search for events on certain dates, order events by dates etc.) and actually reduces data redundancy.
events {id, name, etc}
dates{id, date}
event_dates{id, event_id, date_id}
It might seem wasteful to use 3 tables for this, but it gives you more flexibility (search for events on certain dates, order events by dates etc.) and actually reduces data redundancy.