Serialize data or individual entries suggestion

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Roaches
Forum Newbie
Posts: 3
Joined: Fri Feb 22, 2008 7:33 pm

Serialize data or individual entries suggestion

Post by Roaches »

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?
waylon999
Forum Commoner
Posts: 26
Joined: Mon Mar 23, 2009 5:29 pm

Re: Serialize data or individual entries suggestion

Post by waylon999 »

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.
Post Reply