[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Calendar Application Db / Interface design

Posted on 2006-03-28
11
Medium Priority
?
236 Views
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.

David.

0
Comment
Question by:DavidBook
  • 5
  • 4
9 Comments
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16318960
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
 
LVL 3

Author Comment

by:DavidBook
ID: 16319685
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
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16319706
SELECT Task FROM MyTaskTable WHERE
        UserID = 'GavinMannion' AND
        GETDATE() BETWEEN  StartDate AND  EndDate
AND
       DayOfWeek = 2
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:GavinMannion
ID: 16319755
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
 
LVL 3

Author Comment

by:DavidBook
ID: 16319848
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
 
LVL 3

Author Comment

by:DavidBook
ID: 16324778
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
 
LVL 15

Accepted Solution

by:
GavinMannion earned 2000 total points
ID: 16329788
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
 
LVL 3

Author Comment

by:DavidBook
ID: 16330852
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
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16331416
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question