Defining the Subscription Schema

Notification Services generates notifications by using Transact-SQL queries that join event and subscription data. For most applications, you must specify the subscription fields that are used when generating notifications. You specify these fields in the schema of the subscription class.

This topic describes the default fields that are added by Notification Services, standard fields that you can add to the schema, and how to create custom fields.

Default Subscription Fields

All subscriptions require common information. Notification Services must be able to uniquely identify each subscription, identify the subscriber for each subscription, and determine if the subscription is enabled. If the subscription class supports scheduled subscriptions, the subscription must be associated with a subscription schedule.

To simplify application development, Notification Services automatically adds the following subscription class fields for this common information:

• Notification Services includes SubscriptionId, SubscriberId, and Enabled fields. When Notification Services creates the application, it adds the following columns to the subscription table definition:
  • SubscriptionId is a bigint identity column. Notification Services provides a value for this column for each new subscription.
  • SubscriberId is an nvarchar(255) column. Your subscription management interface must provide the subscriber ID value when you add a subscription.
  • Enabled is a Boolean field that you can programmatically set for each subscription. Setting the Enabled value to false suspends notification generation for the subscription. The default value is true.
• If the subscription class has scheduled rules, Notification Services includes a ScheduleId field. In the subscription table, this becomes a foreign key to a subscription schedule table. The schedule value is defined in a subscription management interface when the user creates a scheduled subscription. The ScheduleId is an int column that allows NULL values, in case the subscription class supports both event-driven and scheduled subscription.

Standard Fields: DeviceName and SubscriberLocale

Depending on your application, you might want to define two fields in a subscription class schema: DeviceName and SubscriberLocale. These fields are often defined in each subscription class to allow the subscriber to select a device and locale, but they are not required. If the fields are defined, the distributor can use the information for notification formatting and delivery.

DeviceName Field

A subscriber device is where a notification is delivered. A device can be e-mail, Windows Messenger, a cellular phone, or any other device that your application supports.

When you generate notifications, the notification data must include the device name for notification delivery. If you allow subscribers to specify a device when they create a subscription, include the DeviceName field in the subscription class schema.

Use the following properties for the DeviceName field:

• Field name: DeviceName
• Field type: nvarchar(255)
• Field modifier: NOT NULL

Information about the device, such as the subscriber's address, is in the subscriber data in the instance database. Use the NSSubscriberDeviceView view to review subscriber and subscriber device information for an instance of Notification Services. For more information, see NSSubscriberDeviceView.

If you exclude the DeviceName field from the schema, you must also do one of the following:

• If your application supports only one device, provide a static device name in the Transact-SQL rule used to generate notifications.
• If your application supports multiple devices, select a device dynamically. For example, a subscriber might have multiple devices, and your application might select the device based on time of day. During business hours, you could send all notifications to a subscriber's business e-mail account. During off-hours, you might send critical notifications to the subscriber's phone and all other notifications to the subscriber's home e-mail account.

SubscriberLocale Field

A locale is a language and country/region for the notification. Notification Services supports many locales. For a list of locales, see Subscriber Locale Codes.

When you generate notifications, the notification data must include the locale. If you allow subscribers to specify a locale, include the SubscriberLocale field in the subscription class schema.

Use the following properties for the SubscriberLocale field:

• Field name: SubscriberLocale
• Field type: nvarchar(10)
• Field modifier: NOT NULL

If you exclude the SubscriberLocale field, you must do one of the following:

• If your application supports only a single locale, provide a static locale in the Transact-SQL rule used to generate notifications.
• If your application supports multiple locales, select the subscriber locale from another source, such as a user profile table in another database.

Custom Subscription Fields

Most applications also have custom subscription fields. These fields define values that subscribers can customize in their subscriptions and are used to create columns in the subscription table.

Creating custom fields requires you to know what information subscribers can provide to customize their subscriptions. For example, a weather application might allow users to select only a city, so you would only define a 'city' custom field. A flight application might allow users to select a flight origin, destination, price, and carrier, so you would create fields such as LeavingFrom, GoingTo, Price, and Carrier.

When you define a custom field, you must also define the field's SQL Server data type and any field modifiers, such as whether the field allows a NULL value. Because these fields define table columns, you must follow the guidelines for defining table columns. For more information, see CREATE TABLE (Transact-SQL).

Defining the Schema

In summary, when you define the schema for a subscription class, you define custom fields and, if used, the standard DeviceName and SubscriberLocale fields. You do not define default fields, such as SubscriberId.

To define the schema for a subscription class

If you are defining an application using XML, define the schema for a subscription class in the application definition file (ADF). If you are defining an application programmatically, use Notification Services Management Objects (NMO) to define the schema for a subscription class.

• Schema Element for SubscriptionClass (ADF)
• SubscriptionFields (NMO)

Examples

For schema examples, see Subscription Schema Examples.

See Also

Concepts

Defining the Core Subscription Class
Defining Subscription Rules
Defining Indexes for a Subscription Class
Defining Chronicles for a Subscription Class
Collecting Subscription-Related Information

Other Resources

Defining Subscription Classes
Data Types (Database Engine)
CREATE TABLE (Transact-SQL)
FieldType Element for SubscriptionClass/Schema/Field (ADF)
Developing Subscription Management Interfaces

Help and Information

Getting SQL Server 2005 Assistance