Requirements for an online database driven reminder service

An email client application typically has an ‘outbox folder’ where emails can be stored to be sent at a set time. For example you could create an email and set the option to send it late in the day.  A limitation of this is that the user may have to remain logged in for the message to be sent and for that matter, the application would have to be running to send the messages.

So given the limitations above how to make a better email marketing application that is more robust? That is the question and I only know there are many approaches.

But what are the requirements? The program needs:

  • to be simple to set up and maintain by anyone
  • send date set by the user
  • subject and body set by the user
  • process the email messages automatically

So that is the bare essentials, it leaves out a lot of user interface features that might be desirable but for now we want to keep it as simple as possible.

So with that said we could use ms-access, perhaps integrating it with outlook using the vba programming language. This provides some good benefits, a ready made environment from which to easily build future features.  You could store the emails locally in ms access tables, or link to a SQL Server database. For that matter ms-access can link to ms-excel or text files. There are a lot of different options. Ms-Access is a very flexible platform. However, the downside to ms-access running on a client is it is tied to the machine running it. If you have to maintain this database you may have to physically go to the computer where it resides or remote into it. Unless it is a dedicated machine, you might be working on a computer that has multiple business functions, which you run the risk of taking down.  And besides, ms-access isn’t designed to run as a server.

Another option is to use a program like the heartbeat example from a few posts ago and have it poll a sql server database looking in table rows for any reminders that need to be sent. After it sends the message it will either just mark it as ‘sent’ or delete the row.  I’m also attracted to the idea that this could be an online database interacting with the customer but making it possible to server more than one customer. In terms of how to maintain it, it the sql server were hosted by a web hosting service it would be easier to maintain and debug.

Of the two options, I have already done the first option. I know it is possible and can work even with the issues mentioned. However due to the benefits of the second choice, I feel it would be better, more stable, scalable way to go.

I am confident as long as I can access the sql server from anywhere,  and I have access to the c# source code, I can debug or update features without interrupting or inconveniencing anyone.   So, in a future post we will inch our way ahead to making our reminder database driven service using ms-sql and the c# classes from the prior posts.

There is however one downside that bothers me about online databases, that is they are more vulnerable so extra care must be taken. In particular, our database would store email addresses and I am concerned to make sure that data can be kept safe. One possible way to go is to encrypt the data. There are some options that dot net has. In the next post I’ll create a working demo showing how one can set up encryption.