We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Calendar Application Db / Interface design

DavidBook asked
Medium Priority
Last Modified: 2008-03-10
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.


Watch Question

From what I understand of your question you would want something along the lines of

TaskID (int) -- UserID (varchar) --- Task (varchar) -- BeginDate (DateTime) -- EndDate (DateTime) -- DayOfWeek (int) -- Completed (bit)

So if I want to phone my wife every Tuesday for the next year I would insert the record

1 -- 'GavinMannion' -- 'Phone the wife' -- '01/04/2006' -- '01/04/2007' -- 2 -- 0

The select would be

        UserID = 'GavinMannion' AND
        StartDate < GetDate() AND
        EndDate > GetDate() AND
       DayOfWeek = 2


um, that makes sense. But, still things I dont' understand, such as:

viewDay.aspx, viewWeek.aspx, viewMonth.aspx.

View Day, how would I show items due on a certain day? View Week, how would items be shown on a certain week?

The idea is to show the user a list of items that were scheduled along with an indicator of whether they were "marked done" or not.

How would I show if an certain item has been done, it may be scheduled "every day" for the next year and only some of the occurances may have been done?

Your already helping!!!!! David.
        UserID = 'GavinMannion' AND
        GETDATE() BETWEEN  StartDate AND  EndDate
       DayOfWeek = 2
Sorry hit submit to quickly.

The above post works when you send a specific date and day of the week. So that would be your day view.

To get the whole week try

        UserID = 'GavinMannion' AND
        GETDATE() BETWEEN  StartDate AND  EndDate

If you are trying to set specific dates for specific tasks then just set the start date and end date to be the same date.

I am not so sure the weeks view will work..... Give it a bash and let me know......


ah, I see, it's starting to make sense. I'll give it a try. I'm not at home so it might be a bit (tomm?). Gotta re-open the app on my machine ;-)

I'll let you.

cheers, d.


Ok, going backwards. Thought I had it figured out but don't afterall. It seems the issue is the "Mark Done" function. Currently, when an item is marked done it has notes modified and some other basic info. like who did it, etc. Since each scheduled item is a unique record it is easy to make changes to it's status/notes/etc. without affecting the other items. In our example, (add one record that repeats) I still can't figure out how to keep track of which occurances have been marked done.

The sql you supplied will work for returning what is scheduled and will work for showing "whats due" when looking at a calender.


Hmm, okay so you want a repeating task that will [popup] every Wednesday and then the person will do their task and mark it done but it then must popup again the next Wednesday?

If this is the case then you will need to add in another pile of business logic.

Basically my initial guess would be you need another table where you would mark the task as done and add the notes and then your SQL statement will have to join on that table and check whether or not the task is done or not.

....... I think this is a bit further than my SQL knowledge will allow ;)

Basically you would have a second table

TaskCompletedID (int) -- TaskID (int - link to other table) --- UserID (varchar) -- CompletionDate (DateTime) -- Comments (varchar)

Then you would need to join on this table in your select statement

      OUTER JOIN MyCompletedTasks MCT ON MCT.TaskId = MTT.TaskID
        MTT.UserID = 'GavinMannion' AND
        GETDATE() BETWEEN  MTT.StartDate AND  MTT.EndDate
       MTT.DayOfWeek = 2
AND (MCT.TaskCompletedID IS NULL AND MCT.CompletionDate <> GetDate())

I don't know if the SQL is correct but you want to only get the tasks that do not have a corresponding ID in the completed task table.

If this doesn't work you might want to post a pointer question in the SQL TA so that the SQL experts can answer this.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Tons of help so far! I think I may end up HAVING to insert a new record for every item, this way the item can be modified, etc, etc. This would prevent another HUGE table of "done reports" or whatever. The only reason it doesn't seem ok now is the fact that i do an insert for every occurance of repeating items. I thought I could store less data by not inserting a new record every time, BUT if I have to then store a "done report" somewhere else, it's kinda all for not. Maybe I just won't allow users to "repeat" more than like 10 times??

pondering... I'll let you know how it comes out. If someone doesn't show a better solution in the next day or two I'll award the points to you and move on ;-).

Thanks again.
The only reason I wouldn't create a new record for each one if because you get to save half the space.

Instead of having 10 records of 10 fields each (100 fields) you would have 1 record of 5 fields and then another 10 records of 5 fields. (55 fields)

But it does get a lot more complicated.

If you can delete the records after 3 months then you are never really going to run into many problems on SQL. I have tables which have 3.2m records and they still run fine. (before someone shouts at me I do archive and it is a relational database. We are just a big company:)...)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.