Generating "reminder" for the user

In a mid to large scale application, often you need to run scheduled tasks which can be schedule through the SQL Agent. One type of the schedule is to generate reminder or alert for the user. For example, inform the collection department about the overdue invoice.

Below is the strategy to implement this type of process:

1. Create a stored procedures to scan the data.
2. In this stored procedure, it contains the business logic about what data will meet the requirement and then generate the reminder record. 
3. The reminder record will be saved into tb_mail (this table stores the email to be pickup by a C# program).
4. Develop a program in the C# application which will send all the emails in tb_mail table.

In case you don't want to notify the user through email, you may append this reminder record into an "alert" table (which can be "tb_log" table in our design). Then, display the alerts upon user login.

For those alert requires immediate attention, you may send the reminder or alert through SMS (short messaging).