Implementing Business Rules


As discussed in Chapter 1, business rules are often an importantnay, criticalcog in your application. Business rules, by their very nature tend to morph over time to reflect changes in the business climate, marketing, sales, and relationships with customers. If these are hard-coded in your applications, they must be redeployed when they change. At EDS, much of my data in the Part B healthcare support team was spent changing applications to reflect the ever-changing federal regulations that governed how we adjudicated claims. These business rules could be in the form of default, minimum, maximum, usual, prevailing, or arbitrary values, as well as masks (for specific numeric layouts like phone numbers and social security numbers) and other inter-column data relationships. For example, a claimant could have his left leg removed below the kneebut only once. They're permitted only one appendicitis operation, one hysterectomy, and only two radical mastectomies.

Sure, your business rules probably won't have to be quite as grizzly, but they're nonetheless important. As you deal with hotel rooms, airline seats, customer discounts, and sales commissions, there are bound to be rules your business establishes to manage these values. These rules check to ensure that the rules meet the established criteria for validity and to maintain referential integrity in the database. That is, suppose you have a list of valid parts suppliers. When you add a new order, the Supplier column should contain a valid SupplierID drawn from the ValidSuppliers table. Referential integrity makes sure that's so. Data validity business rules ensure that QuantityOrdered is within the reasonable range of 1 to 50 when the part ordered is a 50-lb. anvil.

Just how (and in which tier) you implement your business rules is up to you and your application architect. Some feel that applications are not a good place to validate these rulesit can mean that the application must be recompiled, tested, and redeployed each time the rules change. That's simply unacceptableeven when the applications are redeployed through one-touch or via a web application. Some think that business rules should be implemented in a middle-tier object layer. Sure, this is a good idea, in some casesespecially if your application starts getting large. However, in this case, you'll likely spend quite a bit of resources designing and building objects, training everyone on your team (now and for the foreseeable future) how to use them, as well as fighting unique "object" issues that simpler designs don't encounter. Some think that business rules should be implemented in stored procedures where they become a part of the database and can be managed centrally. This assumes your team knows how to write and manage shared stored procedureswhich is not a universal truth.

I'm of the opinion that business rules should be executed as close to the point of data collection as possible but designed so they can morph automatically when the rules change. This means creating an infrastructure within the application to capture the latest rules and modify the client-side variables that they govern. This approach can be implemented any number of waysby reading the business rules from SQL Server database tables, by reading them from shared files, or by loading them from a SQL Server Everywhere databasemy favorite solution.

Implementing Business Rules with Extended Properties

Another approach that I've advocated for years uses a unique SQL Server feature called Extended Properties. BOL clearly describes how Extended Properties can be created, modified, or deleted, but there is little guidance beyond a few suggestions on how these properties can be used. That's the point. Their use is limited only by your imagination. I think Extended Properties could easily be used to help implement automatically morphing business rules.

Extended Properties can be assigned to virtually any SQL Server object, including databases, tables, columns, views, rules, triggers, stored procedures parameters, constraints, and indexes. You can create Extended Properties using system-stored procedures or through SQL Server Management Studio (as shown in Figure 2.54)which is far easier to use.

Figure 2.54. Viewing or managing Extended Properties.


Extended properties are added, updated, and deleted using the dialog shown in Figure 2.54 or by calling three system stored procedures:

  • sp_addextendedproperty: Adds a new extended property to a database object

  • sp_updateextendedproperty: Updates the value of an existing extended property

  • sp_dropextendedproperty: Drops an existing extended property

Once the Extended Properties are stored in the database, your application can retrieve the values by name using a system function, FN_LISTEXTENDEDPROPERTY.

At first blush, these stored procedures can be somewhat daunting, but once you understand how the parameters address individual objects or groups of objects, they're pretty simple to use. Books Online (BOL) provides a table (shown in Table 2.5) that describes what values the stored procedure parameters can be set to and what combinations are valid:

Table 2.5. Valid Extended Property Stored Procedure Parameters

Level 0

Level 1

Level 2

User

Table

Column, index, constraint, trigger

 

View

Column, INSTEAD OF trigger

 

Schema-bound view

Column, index, INSTEAD OF trigger

 

Stored procedure

Parameter

 

Rule

<None>

 

Default

<None>

 

Function

Column, parameter, constraint,

 

Schema-bound function

Column, parameter, constraint

User-defined data type

<None>

<None>


Take a look at the sp_addextendedproperty stored procedure. It accepts eight parameters. Figure 2.55 illustrates a typical example that sets an Extended Property named "caption" with a value of "Employee ID" on table (Employee), column (id), and owned by user "dbo".

Figure 2.55. Adding an extended property to the Employee.ID column.


The first two sp_addextendedproperty parameters name the Extended Property and the value. Since the Value argument is a sql_variant, you can pass anything except a BLOB (see the earlier discussion and BOL for more details on the sql_variant).

The six remaining sp_addextendedproperty parameters point to the objectin this case, a specific column. Notice how the fifth sp_addextendedproperty parameter names the "Level 1" object type ('Table', 'View', 'Rule', etc.) and how the sixth "Level 2" parameter names the object itself ('Address' in the previous example). For example, to add an Extended Property to a SQL Server table object, you need to pass two levels to sp_addextendedpropertythe user and tableas parameters. The remaining level 3 parameters can be omitted. For example, to set a couple of Extended Properties on the Publishers table, you could code a batch as shown in Figure 2.56.

Figure 2.56. Adding two Extended Properties to the Publishers table.


An Easier Way

While you can use system stored procedures to manage Extended Properties, it's far easier to use SQL Server Management Studio. To view or manage Extended Properties, right-click the object in the object tree view and click "Properties". Next, select "Extended Properties", as shown in Figure 2.57. In this case, I'm addressing the Publishers table in the sample Biblio database.

Figure 2.57. Managing Extended Properties using SQL Server Management Studio.


The Extended Properties dialog is intuitive and easy to use. However, consider that the dialog does not care if you add more than one property with the same name. Since the entries you make are not committed to the database until you press "Apply" or "OK", duplicate entries are not picked up until the dialog attempts to update the database. Notice that the Value field accepts Unicode strings. This same dialog is used to add Extended Properties to any of the supported objects.

In your application, you'll want to retrieve the Extended Properties and use the values to morph your client-side business rules or simply change the captions for the data columns being managed. SQL Server provides a system-level Function for this purpose: FN_LISTEXTENDEDPROPERTY. Its parameters follow the same hierarchical pattern as the stored procedures used to manage Extended Properties. That is, to return a specifically named Extended Property, you pass all three levels to the function. By passing "NULL" or "Default" instead of one or more levels, the FN_LISTEXTENDEDPROPERTY returns all of the Extended Properties under the specified level. For example, the example shown in Figure 2.58 returns all defined Extended Properties for the Publishers table (in the Biblio database).

Figure 2.58. Returning all of the Extended Properties for the Publishers table.


One of the problems I expect you'll have with SQL Server Management Studio's query editor (I know I've hit it often enough) is that if you don't specify the correct default database before executing, your query won't do what you expect. It's good practice to add the USE statement at the head of your queries so they always do what they're toldto the correct database.


The first query in the example in Figure 2.59 returns the database object's Extended Properties but none of the database's subobjects (such as Tables). The second and third examples return table and column Extended Properties for the Publishers table.

Figure 2.59. Using fn_ListExtendedProperty to return selected Extended Properties.


Other Extended Properties Issues

Another, more complex challenge faces developers who use Extended Properties: discipline. That is, there is no standard way to define these name-value pairs. The Extended Property names aren't case-sensitive (unless the database is set up to be case-sensitive), but if your company does not standardize on a naming convention and rules that dictate how and where Extended Properties are defined and who can define them, you'll be welcomed into your own world of "DLL hell." This is the same challenge facing developers building COM components or stored proceduresany shared code technology. Other developers who use and reference these objects hard-code the names given to these COM properties, methods, SQL Server stored procedures, and their arguments. These shared-code reference names cannot be permitted change once the class, object, or stored procedure is published and developers begin to code against them. While it's easy to make these changes (the latest GUI tools make it easier than ever), your team's discipline and training must make sure that these names aren't rearranged, their data-types aren't changed, and the arguments remain the same. While it's possible to add arguments to existing objects, you must maintain backward-compatibility with the previous version. But you knew that.

Extended Properties can easily make a significant impact on your application's ability to better manage business rules, how your applications (or web pages) deal with data-driven displays and validation rules, and much, much morelimited only by your imagination. I envision use of these properties to manage business rules or user-interface parameters for the data. Imagine being able to set minimum, maximum, default, today's value, suggested value, field masks (###,###.00), localization strings or any number of attributes that can change from time to time. When the application first starts, it can read the Extended Properties and morph itself to the new properties.

Implementing Business Rules with T-SQL Rules, Constraints, and Defaults

Another, more traditional approach to handling business rules has been around since the earliest days of SQL ServerT-SQL Rules. While (again) this approach is not in vogue, it's still supported (at least, until Microsoft tries to pull it) and a very understandable way to handle this problem. As I see it, as Microsoft has attempted to conform to SQL ANSI standards, many of the traditional (and well-understood) techniques have fallen from favor. Yes, in years to come, Microsoft might find a way to replace Rules, but I don't see that happening anytime soon. I would keep using them as long as they make sense. Microsoft says that Rules are provided in SQL Server 2005 for "backward-compatibility." Microsoft wants developers to use CHECK constraints as the "preferred" and "standard" way to restrict column values. They're rightonly one Rule can be applied to a column, while any number of CHECK constraints can be applied. But CHECK constraints can't be applied to User Defined Typesas you can with Rules. Rules and CHECK constraints are also managed differently, as the following discussion highlights. Yep, I'm still a Rules fan.

T-SQL Rule operators are not that well documented in BOL (although they used to be). Let's step through the process of creating T-SQL Rules and how they can report failures back to your application. Think of a Rule as a WHERE clause for a new row's column(s). That is, if the conditions set by all column Rules are met (as in a WHERE clause), the row is accepted into the table. Rules can be associated with a specific column or a T-SQL User-Defined datatype (UDT) assigned to a column. No, these UDTs are not the ones you can create with the Common Language Runtime (CLR) that I discuss in Chapter 13, "Managing SQL Server CLR Executables" these are the old-fashioned user-defined datatypes that are simply aliased SQL datatypes. I discuss T-SQL UDTs in the next section, so you might want to skip down there to get a feel for how they work before I go onjust remember to return here when you're done.

When you assign a rule to a T-SQL UDT, you define the special criteria that must be met whenever a column is defined with the UDT. For example, you can create a UDT for the Discount column and assign a Rule to the UDT (instead of directly to the Table). This rule might limit the values applied to the Discount column to values between 0 and 99. As you can see, this goes well beyond simple type checking. Sure, it's important to test to ensure that the value supplied is an integer, but that still permits invalid values from being passed to the Discount column. Yes, a Constraint can also be applied to the column to perform the same check. However, when the business rules change, you must revisit each and every column Constraint that validates the Discount value. I've mentioned this shortcoming to the SQL Server team on several occasionsthey say their hands are tied by ANSI requirements.

Creating Rules

Ah, Microsoft has disabled the ability to create new Rules from the SQL Server Management Studio IDE and they're not even listed as visible with Visual Studio. This means your only option if you want to create a rule is do it the hard waythe way we created Rules for yearsusing a T-SQL editor. Since rules are very easy to write, this should not be a problem. Do you think they're trying to hint at something? I expect so.

When you want to create a rule, simply execute the CREATE RULE statements, as shown in Figure 2.60.

Figure 2.60. Creating a TSQL Rule.


Once the Rule is created, it can be bound to any specific column in the databasebut only one rule can be applied to a column. This means if the logic in the Rule needs to use more complex validation logic, it must be included with AND or OR expressions. No, a Rule cannot reference any other column in its logic. That might be nice, but that's really the prevue of a Triggerwhich I discuss later in this chapter. Note that the variable (@Discount), in the example in Figure 2.61, can be anythingit just has to start with the "@" character.

Figure 2.61. Testing for a valid ZIP code layout.


Rules are handy for making sure a column conforms to a specific pattern as well. For example if you want to ensure that a U.S. postal code (a ZIP code) conforms to postal regulations, you can create a Rule, as shown in Figure 2.61.

Binding Rules to a Column

Okay, now that you have a defined set of Rules, you need to decide how to bind them to the columns to be validated. This can be a lot of workespecially if the business rules implemented in the T-SQL Rule changes from time to time. To address this problem, most DBAs script their Rules and Rule Binding operations, and when it comes time to change the business rules, they alter the script and change them all en masse. No, you can't bind a rule to BLOB, CLR UDTs, or TimeStamp columns.

To bind a Rule to a specific column, you must execute the sp_bindrule system stored procedure, as shown in Figure 2.62.

Figure 2.62. Creating and binding the ValidDiscount rule in a script.


Okay, so what's a script? Well, hold on for a few pagesI'll show you how they work a little later. Suffice it to say they're simply a set of T-SQL commands in a single file that are executed in sequence. Each statement is separated from the previous by the "GO" operator. In this example, we make sure the default database is "Biblio", create the Rule, and bind it to the Customers table Discount column. Before I go on, let's talk about User-Defined typesthe ones that are managed by TSQL.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net