Visibility schedule

Question

I want to have a simple database table to keep track of scheduled category visibility on a site index. Basically it will tell the index to display a Christmas category between Thanksgiving and Christmas day.

So far I'm thinking of using a table like this,

schedule_id SMALLINT,

start_date TIMESTAMP,

end_date TIMESTAMP,

category_id SMALLINT,

annual BOOL

Simple enough, the database can select any rows where the time falls between start/end.

My issue comes in with my annual flag. Basically I'm thinking that once a day the database can update the table, and add one year to any row where end_date < now and annual is true, otherwise delete the row if it's expired.

I'm worried that leap years or something could offset the rescheduled date.

Am I on the right track here ?

Is there a better way to do this ?

Answer 1

If you add one year, rather than (say) 365 days you won't need to worry about the leap year issue.

 ADDDATE( date, INTERVAL 1 YEAR )

Adding 1 year to a leap day date gets you 28th February for the following year

2008-02-29 -> 2009-02-28

You might consider breaking your schedules out from your categories and having a join table linking categories to schedules. Then if you want to share a schedule betwen categories its a little easier. And potentially you can use the schedules elsewhere by adding other join tables.

Last suggestion, rather than having an annual field, have a nullable field to indicate a periodicity: weekly, monthly, etc., again gives you a more flexible schedule model.

HTH