I might not fully understand your data but it appears the dates should not be store in the same row (the way the data is presented to a user does not have to be reflected in the underlying tables - you just need to be able to extract it in the required format). If you add the completed field to the table designs it become more apparent these 2 items of data should be grouped also consider if more dates were required say 6 month date or 2 year date could these changes be easily accommodated?
Would this work: a table, I'll call it program_dates, with the following fields:
program name (char)
type (char)
prog_date (date)
complete (char Y or N) -- you mention tinyint(1) if this allows qualifier of TRUE or FALSE use that if not read something = 'Y or 'N' is more read able than = 0 or = 1.
so each program would take 4 entries
Code:
programme_name | type | prog_date |complete
----------------------------------------------------------------
Woop | 1 week date | 01/03/2008 | N
Woop | 1 month date | 23/03/2008 | N
Woop | 3 month date | 23/05/2008 | N
Woop | 1 year date | 23/02/2008 | N
Blah | 1 week date | 02/03/2008 | N
etc
so to list the un-complete ( complete ='N') order by date
Code:
select programme_name,
type ,
prog_date
from program_dates
where complete ='N'
order by prog_date
The last thing to do is extract the data in your required format - can mysql do a pivot?
If not you can join table programme_name to itself
Code:
select d1weekdate.programme_name,
d1weekdate.prog_date, /* '1 week date' */
d1monthdate.prog_date /* '1 month date' */
d3monthdate.prog_date /* '3 month date' */
d1yeardate.prog_date /* '1 year date' */
from program_dates d1weekdate,
program_dates d1monthdate,
program_dates d3monthdate,
program_dates d1yeardate,
where d1weekdate.programme_name = d1monthdate.programme_name /* table join */
and d1weekdate.programme_name = d3monthdate.programme_name /* table join */
and d1weekdate.programme_name = d1yeardate.programme_name /* table join */
and d1weekdate.type = '1 week date'
and d1monthdate.type = '1 month date'
and d3monthdate.type = '3 month date'
and d1yeardate.type = '1 year date'