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

Posted on 2009-05-13
Medium Priority
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?

Question by:missionarymike
  • 5
  • 4
LVL 81

Expert Comment

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.


Author Comment

ID: 24377113
Thanks for the reply.

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


Author Comment

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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 81

Expert Comment

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.


Author Comment

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.

LVL 81

Accepted Solution

arnold earned 2000 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.

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


Author Closing Comment

ID: 31581043
That is awesome.  

Thank You.

Author Comment

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

LVL 81

Expert Comment

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,
365 yearly/annual

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?

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

624 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