SSWUGtv
With Stephen Wynkoop
If someone asked you, "as a DBA…what are the key things you need to know", what would your answer be? Hear SQL Expert Craig Mullins share his biggest DBA tips in this episode of SSWUGtv.
Watch the Show
Check Constraint Implementing Business Rules
In a series of Email Exchanges with Annamnidu we were discussing implementing of business rules in the database. This is a topic I keep coming back to because I believe it is one of the cornerstone elements of any application. Where do you put your rules?
Personally, I say they belong in many places. You need business rules in your application tier. You also need business rules in your database schema. But, they may be redundant. Even so, you need to go to the effort to maintain rules in both places.
Here is a great example Annamnidu proposes. He writes,
Let me take an example and see if I understood your concept.
If we have an application to book a flight, a few business rules are
Rule #1: From and To Dates must not be blank
Rule #2: From and To Dates must be valid dates
Rule #3: To Date must be after From Date
Do you think we can have all rules (1 to 3) can be developed in Stored Procedures, functions etc in the back end?
This is a great example where rules belong in both the database schema and in the application.
All of these rules should be validated in the database through table definitions and constraint. There is no need for stored procedures or functions to validate these rules. Solid a solid table definition with a check constraint works perfectly.
CREATE TABLE Flight (
TicketID INT NOT NULL IDENTITY(1,1)
,FlightStartDateTime DATETIME NOT NULL -- Enforces valid Date and Not Empty
,FlightEndDateTime DATETIME NOT NULL -- Enforces valid Date and Not Empty
,CONSTRAINT ckDatesSequential CHECK (FlightStartDateTime < FlightEndDateTime) -- Enforces first date before second date
)
--This failes because start and end date are exactly the same
INSERT INTO Flight SELECT GETDATE(), GETDATE()
--This definitely fails because end is 1 day less than start
INSERT INTO Flight SELECT GETDATE(), DATEADD(dd, -1, GETDATE())
--This insert works fine
INSERT INTO Flight SELECT GETDATE(), DATEADD(dd, 1, GETDATE())
SELECT * FROM Flight
However, as an end user I would hate to go through a number of screens entering data only to find out at the end when the data is being persisted that the data cannot be saved because I violated a business rule.
For this reason, this same business logic should be duplicated in your application layer. On a web form, using Model View Controller you could have this validation logic available for use in any tier of your application for validation. The sooner you fix data entry errors the better.
So, if you are going to put these rules in your application why would you place them in your database as well? The reason is simple…you need to protect your database from allowing badly formed data. NOT NULL should be the norm for fields. The check constraint is an extra bonus. This way if you have some application working with your data that is outside your business rules you have some degree of protection.
Perhaps this example of Business Rules helps clarify the issue for you. Send you suggestions or comments to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with SQL Server 2008 R2 performance counters
In this article, Alexander Chigrik explains some problems that you can have with SQL Server 2008 R2 performance counters.
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)