DavidBook
asked on
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.
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.
ASKER
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.
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.
SELECT Task FROM MyTaskTable WHERE
UserID = 'GavinMannion' AND
GETDATE() BETWEEN StartDate AND EndDate
AND
DayOfWeek = 2
UserID = 'GavinMannion' AND
GETDATE() BETWEEN StartDate AND EndDate
AND
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
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......
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......
ASKER
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.
I'll let you.
cheers, d.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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:)...)
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:)...)
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