Calendar Application Db / Interface design
Posted on 2006-03-28
Hi gang, hope this is easy to understand. I have a calendar type app that allows the scheduling of "to-do items". When scheduling an item it allows the user to "repeat" an item x number of times. The app is working but has a serious flaw ( I think ). If a user repeats an item 10 times, 10 records are inserted in the db in tblScheduledItems. Each item is later marked done, notes added, etc. I need to allow the user to repeat the item "forever" (or for longer time periods) and / or repeat the item until a certain date. This is common in other calendar type apps.
My concern: If a user repeats an item "forever" I can't possibly insert a new record in tblScheduledItems until ?? If a user repeats an item 100 times the same is true, why insert 100 records for "one" to-do type item. Currently I insert a unique record for each item because after an item is scheduled it is independent of other items. One item may get marked done while another item is ignored, get it? So, how can I allow users to repeat items "forever" without inserting a zillion records into the db AND allow the user to independently mark the items done? How would I query the db for items due on a certain date, or between certain dates, etc, etc.
What I need is a basic table design to hold the scheduled items and some logic (SQL) to query for items to display on the calendar screen.
I hope this makes sense.