How to structure database for combined storage of weekends + annual off/closing days?
Situation:
A media screen that plays advertisements throughout an 开发者_C百科year would be closed on certain days in an year. The closing/off days would be specified in the following two ways: 1. Annual holidays (different for each year). 2. Weekly off scheme (eg. sundays off, saturdays+sundays off, tuesdays off, second saturdays off etc). These could be the same for every year until altered in the database. Problem: Assuming we have a database table viz. 'screens' with fields 'id' and 'name', how to modify the structure of the table to store the combined closing days scheme for a particular media screen.Confusion:
1. Whether to store the closing/off days scheme in the 'screens' table itself or a separate one? 2. Whether to combine the weekly off scheme with the annual off scheme while structuring the database to store the closing days scheme or not? 3. Whether to store the closing scheme as 'closing rules'(eg 1000000 for sundays off, 1000001 for sundays + saturdays off) or as 'off day dates'(eg 04-07-2011,05-07-2011)? Is there any other way?i would store the close days in a separate table:
close_days
-----------
screen_id
close_date
(optionally... advertisement_id)
then populate this table with all the dates through some external algorithm, or let the user select days randomly.
精彩评论