Solved

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

Posted on 2009-05-13
9
335 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 76

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
 
LVL 76

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 76

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 76

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now