Gathering Requirements

Given that scenario, you begin to design a draft of the database schema. Of course, in the real world, you will begin by creating a list of requirements, some UML (Unified Modeling Language) schemas, and other items before designing the database, but let's compress that process for the sake of simplicity.

Preliminary Requirements

Most likely, the schema includes the items shown in Table 13-1. It makes sense to try to design the application to be reusable for other notification scenarios not related to birding. Therefore, you will name the common elements with generic names, allowing for their reuse without having to rewrite shared items.

Table 13-1. Birding Application Schema Elements



List of regions


List of sightings


List of interested people


List of which region each person is interested in


It happens that the last three names coincide with Notifications Services terminology. The first item, Regions, however, contains application-specific information and has no corresponding Notifications Services term.

Additional Requirements

Bird sightings come in three categories:

  • Sightings of common resident birds in a region, easy to spot all year round

  • Sightings of migrant bird arrivals to a region

  • Sightings of rare birds. These rare birds might stay in the region for a short time and might not be seen again in that region for several years. Thus, some birders would be very interested in knowing about those occurrences.

Therefore, your application must fulfill the following requirements:

  • Subscribers should be able to sign up for immediate notifications for rare bird sightings and/or regularly scheduled notifications for non-urgent alerts.

  • Subscribers should be able to be notified at their preferred device. Advanced birders would like to receive special alerts as SMS (Short Message Service) messages on their mobile phones. Other subscribers might prefer to be notified once a day or once per week through e-mail.

These two requirements lead to some changes in the application schema. We will need to keep track of:

  • Each subscriber's device info Device info includes e-mail address, mobile phone number, etc.

  • Which subscription corresponds to which device For instance, urgent alerts should be sent to a mobile phone, while regular alerts should be sent by e-mail.

  • The frequency for alerts Frequency might be immediate, daily, or weekly.

  • The sighting category Categories for this example are regular, migrant, or rare.

  • Which alerts have been sent to which subscriptions You will need to store actual date and time of an event occurrence as well as the date and time the application is notified. With that data at hand, the notification application should be able to identify which events, for example, have been sent to daily subscriptions and which of them have not yet been sent to weekly subscriptions.

How will you meet these new requirements?

  • Device info for subscribers should be stored in its own table.

  • The device preference for each subscription should be stored along with the subscription information.

  • Timing information should be stored along with the subscription itself.

  • The category will be stored with the event itself, and the observer will provide that value. Thus, when someone enters a sighting in the notification solution, he or she is responsible for categorizing it as regular, migrant, or rare.

Historical Information

Historical information is somewhat more complicated to manage. We need to compare the date and time the event was entered with the date and time for the last notification-sending operation. Our database schema does not include any information about when the alerts were sent. Therefore, we need to keep track of notification processing.

To keep track of notification processing, you can choose between two models:

  • Recording the subscription-type processing date and time. You will record the type of subscription, daily or weekly, along with the date and time you start the sending process. When starting the sending process, the system stores the current date and time. This option results in only two records: one for weekly subscriptions and one for daily subscriptions.

  • Recording each subscription processing date and time. You will record the specific subscription's identifier, along with the date and time you start the processing, or the last event identifier included in the notification.

Which one should you choose? Recording the date by type is simpler than recording it for each subscription. The former requires keeping only one record for each subscription type. The latter approach requires one record for each subscription and some logic to keep both tables (subscriptions and processes) in sync. However, in the event that a notification is not delivered, the type approach will not allow the affected subscribers to ever receive the notification. This drawback might be acceptable in some non-critical notification applications, but it is not acceptable in other kinds of applications, like those involving expected actions on the subscriber's part (a phone call to return, a contract change to act on, a customer claim, etc.). Indeed, even non-critical notification subscribers will prefer not missing any events. Therefore, for the sake of reusability and ensuring completeness, the second approach should be your choice.


In Notification Services terminology, historical data is stored in chronicle tables.

Device Diversity

Some additional requirements arise from having different devices and media to which our application must send notifications. E-mail messages should be delivered by means of an SMTP server and SMS messages by means of a text-message server. Additionally, the e-mail format, usually HTML, is not suitable for sending SMS text messages and vice versa. Therefore, the solution should be able to send different bytes to different server types. This is a configuration issue, but also something to consider in the application architecture.

Again, several options exist to implement this functionality, from hardcoding the message-building process in the application to using third-party software that handles the conversion process. Notification Services solves this issue with openness in mind: you must supply an XSLT (eXtensible Stylesheet Language Transformation) file for each device type (to be comprehensive, you will need an XSLT file for each device and locale combination). An XSLT engine can use an XSLT template to transform an XML input to some other type of output (HTML, XML, or text). Therefore, this is a very suitable way for meeting most requirements.

Notification Information

The application's internal processes will manage information in a format that is not user friendly. For instance, the application refers to every item by ID number instead of by Name. However, when preparing the data for actual end-user delivery, translating IDs into textual information is mandatory.

In order to accelerate the final notification generation and its delivery, you should prepare the information that this process needs. For instance, instead of storing the Region ID, you would store the Region Name. You would also store the Event Description instead of the Event ID. Thus, when the engine decides to send the notification, it will not need to look in several tables and perform joins to get the information it needs. It will simply read a record from a table where all data is prepared according to its needs, ready to be sent out.

Considerations for Performance and Scalability

Although your customer might not mention it, you should consider performance issues. E-mails and text messages should be delivered to subscribers individually. It is common knowledge that writing a record in a table is much faster than sending an e-mail, mainly because of the network latency involved. In the event of notifying a large number of subscribers with a short time between events, a bottleneck might arise that could reduce the application's usefulness. You should think about possible ways of minimizing the delivery process workload. To accelerate delivery times, you have two options:

  • Reduce the time needed to send an individual message

  • Reduce the number of messages to be sent

Reducing the time needed to send an individual message can be achieved in multiple ways:

  • Using faster hardware

  • Using faster network devices

  • Reducing the message size

Whatever you do, the time required to send an e-mail or text message will always depend on a network over which you have no control. Therefore, reducing the number of messages to be sent is a preferable option. By analyzing the notification nature, you might notice that many subscribers share an interest in a given region and that some subscribers have scheduled multiple regions about which to be notified. If so, you can reduce the number of e-mails to be sent by:

  • Sending a single e-mail to all subscribers with identical preferences. For example, all subscribers interested in Birdinia North can receive the same message.

  • Merging all scheduled notifications to a specific subscriber into a single notification so that, for example, a subscriber interested in three adjacent regions receives one message instead of three.


The latter requirements will not be implemented in the scenario you are developing. They are mentioned here for the sake of completeness.

Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: