For me, my design would depends on how often you want to run the process:

1) If you need the notifications to be sent quickly:
   Depending on the delay in notifications, a windows service may be ideal.
It could check the database every x minutes, and then do it's stuff.

2) If you just need notifications once a day:
   Write a console application that sends the notifications based on the
current day.
   Use scheduled tasks to have the process run every day.

Windows Services in .Net are very easy now, although you do still need to be
somewhat careful in the development of them.

Another option is to look at SQL Server Notification Services. This may do
exactly what you are looking for by itself. Or it may be able to atleast
give you notifications about which notifications need to be sent.

HTH

Steve