Solved

What is the best way to design a scheduler/reminder database/userform?

Posted on 2009-05-13
9
352 Views
Last Modified: 2012-05-06
Hello Experts.

I am designing a program in which there is a scheduler/bills reminder that will pop up if the user selects the proper setting and reminds them of events/activities/bills that are due or are due in the next few days based on a a preset "# of days".

Attached is a copy of the database tables.  Please look at them and see if they are accurate and will work properly.

Question:  If the user selects 10 days as the "# of days before" to remind them and then at day 3 they have taken care of the item but no longer want to be reminded of it until next month, how could this be accomplished?

Mike
Scheduler.pdf
0
Comment
Question by:missionarymike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 24376979
1) are you offering the option of 10 days before, 3 days before, oops the event is past due?
2) in any case, the user should have an option to mark the current event as handled such that no subsequent reminders dealing with this event will no longer be needed.
3) you need another table where you will build the notification events possibly a month/week in advance and another table where you will mark acknowledged notifications.
Your reminder will check the two tables to determine whether the event has been resolved.  The mechanism that populates the notification events table, will also consult the acknowledged table to make sure it does not include the event if it has been handled.  This will reduce processing on the notification mechanism since it will not have to go through the 30 scheduled events daily if 28 of those events have been handled at the beginning of the month.



0
 

Author Comment

by:missionarymike
ID: 24377113
Thanks for the reply.

Can you provide a simple diagram like the one I attached?  I am a little confused.

Mike
0
 

Author Comment

by:missionarymike
ID: 24377665
The user will have the option to set the amount of days to engage the reminder.  How will the "handled" system work?

Mike
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 78

Expert Comment

by:arnold
ID: 24380257
Currently you have a user table (scheduler) and an event (schedulertype)
Instead of querying the database on a daily/hourly/minute by minute basis.
Creating another table that will have the events and the scheduled time for notification.

This way you can build the notify schedule table
schedulerID, schedulertypeid,schedulerdatedue_this_month,acknowledged

scheduler-schedulertype a one to many relationship.
instead of schedulertype, why not use a set with the two parameters ('recurring','OneTimeEvent')?

Presumably this is a multi user application.
YOu need user -> bills/events -> type of bill/event.
Then using this information you construct a notification table recalculating on a daily basis. removing entries that have been done, adding entries that need to generate an alert.

0
 

Author Comment

by:missionarymike
ID: 24380458
This is a single user application.
The bills event is simply a scheduled item that is linked to an ExpenseID, so that, part of the query is to search if that bill/ExpenseID has a transaction allocated to it for that month.  If so, then no reminder will be issued.

This seems like a lot of information.  I haven't done this type of database before so I'm a little confused.

1.  Do I keep the SchedulerTypeID table as is with the two options, or build this "set" you mentioned?

2. Does the Scheduler table stay as is?

3. Is the table "NotifyScheduler" table autogenerated at each application load event?  Will the user need to actually input any info into this table?

Thank you again.

You are very detailed.

Mike
0
 
LVL 78

Accepted Solution

by:
arnold earned 500 total points
ID: 24381482
A single table will do just as well since the amount of data for a single user will not be that cumbersome for a query.  You would need a log/record table where you can log that the reminder has been acknowledged as complete.
schedulerdaysbefore should be an integer.

Can the person have multiple events with the same company?

The complexity is that recurring and one time events behave differently such that you would need to incorporate it into your queries/logic.
I.e. the due date for a one time event is month/day/year, but for a recurring event it is day. If you have queterly, bi-monthly or bi-weekly type of events, you have not taken them into account in your design.

The one time reminders are fairly simple: the current date is or is not within the window of the schedulerdatedue.

On the recurring event is where you have to extract the day from the database as well as from the dateadd query where you modify now()+daysbefore to see whether they are within the range.

In the log/record table, you would need to record the month/year for the completed task of recurring events to avoid notifications of current event while at the same time not messing up future ones.

A notification table that builds up makes the event notifier query simpler it either has events reminders or it does not.

The notification table will have a single user input field, done. I.e. when the reminder comes up after the user has completed the task, there should be an option for task/event complete which is different from acknowledged i.e. got the reminder but ...

Here is an example you might find useful.
http://www.codeproject.com/KB/cpp/ReminderApplication.aspx

There are many open source applications that you can use as reference.

0
 

Author Closing Comment

by:missionarymike
ID: 31581043
That is awesome.  

Thank You.
0
 

Author Comment

by:missionarymike
ID: 24407523
Is this what you were talking about Daniel?
Look at the attachment.
Mike

Scheduler-Tables.pdf
0
 
LVL 78

Expert Comment

by:arnold
ID: 24408429
Not sure who Daniel is to whom you addressed the question.But if it was addressed to me, yes.  The SchedulerType is missing what the meaning of the type is.
You probably need an entry i.e. number_of_days that match that period:
7 for weekly,
14
15
30
61
90
182
365 yearly/annual
etc.

SchedulerNotify needs the column of the notification date since this is the table you will be querying within your application on whether today is the day you should notify the user.
You should problably include some additional columns dealing with what the notification should be i.e. is it 10 days out, or has the event passed its expiration and possibly overdue?  once the event is overdue, do you continue to notify the user or is it a lost cause?
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

717 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