How to implement business rules in a Microsoft Sql Server Database?

Business rules are rules or conditions that drive your entire software application system. Business Rules ensure that your data is correct and of use at any point of time. A software application system developed for a Health care domain will have a set of business rules, which may be entirely or partly different from those for a Banking and Financial software system. System analysts while designing the software application system, decide where to implement the business rules – whether on the client side or at the server side. MS SQL Server offers the following means to implement business rules: Constraints, Stored Procedures, Triggers and Rules.

Common examples of simple business rules: A sales order should not have a negative sales amount and should have a unit price, which is greater than zero. Another example is, when a sales order is being generated the stock level should not fall below the zero level. This is required to ensure that there is stock available to fulfill the order. Using an RDBMS, we can incorporate these rules right into the database itself. Let us see how we implement these business rules in SQL Server database by using Constraints.

A Constraint is an object that exists only within the table either at column level or table level. A Constraint is a high-level data validation check or business logic check performed at the database-engine level. Implementing constraints has undergone changes with every release of SQL Server. A common type of Constraint used is Check constraint. Check constraints are useful for ensuring the enforcement of general data-validation rules or simple business rules. A constraint is defined using the same rules that we use in a WHERE clause of a SQL Query.

Examples

To Limit Month column to appropriate numbers

SQL:

BETWEEN 1 AND 12

To Limit to a specific list of shippers

SQL:

IN(‘UPS’,’Fed Ex’,’USPS’)

To ensure Price is a positive number

SQL:

UnitPrice >=0

To Reference another column in the same row and to validate shipment date is always greater than order date.

SQL:

Shipdate >=OrderDate

Note:

Almost anything we put in a WHERE clause can also be put in a constraint.  Constraints are fast compared to the alternatives like rules and triggers.

Another example of a using a Constraint:

In an accounting software database, consider the customer registration table. We need to check for a valid date in a date field while recording the customer registration. Note that we cannot have a date in the system that is a future date. To enforce this validation we use the following constraint.

ALTER TABLE Customers
ADD CONSTRAINT CK_CN_CustomerDate
CHECK
(date<=GETDATE())

If we try to insert a record that violates the CHECK constraint, MS SQL Server will report an error.

Next: Client and Server responsibilities, which a programmer should be aware of.