Solved

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

Posted on 2009-05-13
9
346 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
  • 5
  • 4
9 Comments
 
LVL 77

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 77

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 77

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 77

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

830 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