physical database design for email notification reminders

Question

Dear experts, I am working on design a application that required to send a notification email for paid services. For example, user signed up a hosting solution for 3,6, and 12 months. The application requires to send an email to user whenever the contract expires in a sequence until user renew service for every 2 weeks, 3days and 1 days before contract expires. Could you please help me. I am planing to write an application using c# Asp.net

Thank you.

Answer 1

You can use sql trigger (i guess you'll use SQLServer)

or

Use the task scheduler to launch whatever you want.

Answer 2

The way I've done it in the past, is simply to have an expiry date for the service, you could also have a renewal period (eg 3,6,12).

Simply run a nightly job using whatever scheduling services your language provider to find all customers who are going to expire in 2 weeks, 3 days, 1 days time.

The main issue with this is if the job fails to run for some reason. to work around this, you can keep a log of what notifications they have been sent, so you just find all customers, who are going to expire within 2 weeks who have not been sent the 2 week/3day/1day notifcations.