Calendar Application Db / Interface design

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.

David.

LVL 3
DavidBookAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GavinMannionCommented:
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

SELECT Task FROM MyTaskTable WHERE
        UserID = 'GavinMannion' AND
        StartDate < GetDate() AND
        EndDate > GetDate() AND
       DayOfWeek = 2
0
DavidBookAuthor Commented:
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.
0
GavinMannionCommented:
SELECT Task FROM MyTaskTable WHERE
        UserID = 'GavinMannion' AND
        GETDATE() BETWEEN  StartDate AND  EndDate
AND
       DayOfWeek = 2
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

GavinMannionCommented:
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

SELECT Task FROM MyTaskTable WHERE
        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......
0
DavidBookAuthor Commented:
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.
0
DavidBookAuthor Commented:
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.

Thoughts?

d.
0
GavinMannionCommented:
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

SELECT Task FROM MyTaskTable MTT
      OUTER JOIN MyCompletedTasks MCT ON MCT.TaskId = MTT.TaskID
WHERE
        MTT.UserID = 'GavinMannion' AND
        GETDATE() BETWEEN  MTT.StartDate AND  MTT.EndDate
AND
       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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidBookAuthor Commented:
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.
D.
0
GavinMannionCommented:
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:)...)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.