MySQL/PHP Multiple Dates and Times

I have a table jobs in a MySQL database; - Each job belongs to a user (registered in users) - Each job has dates and times to be executed.

Currently, the dates and times are being stored in serialized arrays of UNIX timestamps (for PHP to use). But this isn't optimal for when trying to retrieve, let开发者_运维技巧's say "jobs for tomotrrow for user 123", because PHP has to first iterate over every job belonging to user 123, deserialize the array and look if the timestamps contained are tomorrow.

Creating a table dates4jobs would preserve atomicity but would still not be optimal because I'd have to query for "all jobs" for "user 123" then look for all "dates" with the corresponding pointer to the user 123's jobs and blablabla...

what do nao? ^^

Just a suggestion: Use a time column in the jobs table instead of the serialization (or both). If you are storing multiple timestamps, it might be a good idea to do your suggestion with the datejoblookup table with a reference to jobs.id and each date. I would discourage manually iterating through the data in PHP. Then you can run your query "jobs for tomorrow for user 123" with one select:

SELECT job.* FROM `jobs` job, `users` user, `datejoblookup` date
WHERE date.jobid = job.id            --make sure the date belongs to this job
AND job.userid = user.id             --make sure the job belongs to the right user
AND user.name = '123'                --select the user with name '123'
AND DATE(date.timestamp) = CURDATE() --select jobs for today

Also think about extensibility and the queries you might need to run in the future. This design would enable queries like "jobs from date1 to date2"