In the course of doing the research for my new book (co-authored with Peter Blackburn), I came across a troubling anomaly. In Chapter 2 I’m teaching new developers how to setup new tables and manage business rules. In earlier versions we used Rules, Defaults and Triggers to manage business rules. We could create a user-defined type (UDT) and assign a rule and default to this UDT. This way if the rule changes, we can easily change the rule anywhere it was used in a single operation—either explicitly bound to a column or indirectly bound via a UDT.
However, I noticed that the SQL Server 2005 help topic for rules now has an admonishment which boils down to “Don’t use Rules. They’re going to be dropped in a future version of SQL Server”. They suggest that developers use a CHECK CONSTRAINT instead. Okay, but to create a CHECK CONSTRAINT, you must do so as the table is being created. To change the constraint you have to execute an ALTER TABLE on each column bound to the constraint. One way around this would be to create a function that performed the check logic. That solves part of the problem, but it does not permit developers to assign a common CHECK CONSTRAINT function to a UDT. This means you have to define the column in terms of the UDT but you also have to remember to add the CHECK CONSTRAINT to the columns. If you miss one by accident (or by ignorance), you don’t get the range or other checking performed by the constraint.
Sure, you could implement the UDT as a CLR routine. Does anyone really think developers are going to change a 4-byte integer column to a (Lord knows how many byte) CLR UDT to manage a simsple business rule? I think that remains to be seen.
When creating a new UDT developers are still prompted (at least in the October and December Yukon CTPs) for a rule. Are these going to be dropped? Is it really necessary to drop rules? Why does Microsoft seem to think that this is going to somehow help customers? Aren’t transitions hard enough without dropping functionality that has worked so well so long?
Bill
This is not really to the point but it has some connection. In my company they decided not to implement any kind of business rules on the database tier anymore, except for data types, not null constraints and unique constraints. This is because they want to centralize all business logic in one place (the middle/business tier that is). That means no more rules, triggers, check constraints, stored procedures/functions. The only thing we use are tables and views.
What's your view on this?
Koen,
Interested in how thats going. I understand the want to put ALL business rules in one spot, which would lead to good separation of the tiers. But I'd like to know from your experience there, how that transition is going or how it went.
Implementing business rules in another “business-tier” has been a popular tactic. How effective this is depends on a number of factors. I think some folks do this because they don’t understand stored procedures or find T-SQL too limiting. People would rather implement the rules in Visual Basic or C# in another tier. Sure, Yukon will permit you to write stored procedures in a CLR language, but for a wealth of reasons this might not be such a good idea. For one thing, the performance penalty imposed by switching to CLR can be expensive—the round trip can be more expensive than simply performing the operation in T-SQL. However, if T-SQL is bogged down because of complex math operations or logic it’s not suited for, it might make abundant sense to switch to a CLR-based function or stored procedure. They’re mostly a replacement for Extended Stored procedures.
I’m not a big fan of creating layers where you don’t need layers. Layers help move logic into specific groups that make team development easier and provide a single place to address business rules, data access interfaces and other related logic. In many cases, rules, triggers, defaults have worked fine. I’ve been told that Rules are passé—first implemented in the “old” Sybase version. While that’s true, I disagree that the usefulness of Rules has passed. I think Rules are easy to understand and the fact that they can be defined globally and attached to user-defined types is a great feature. They say that check constraints are designed to replace them—I’ll believe it when I see it. I think that if Microsoft pulled rules from SQL Server, a lot of folks would march on Redmond—and I can point them to the right building.
There are lots of other strategies to implement business rules. For example, I often suggest that developers use auto-morphing business rules in the client—those that can be easily changed by altering a database element (like an Extended Property). I think you need to implement the technology with which you’re most comfortable.
If you’re a plumber, you solve problems with pipe.
Bill
Ok thanks, I now know that we're not doing wicked things... I personally have the feeling to restrict as most as possible on the database, for the simple reason that your data cannot be messed up, no matter how you access it (except ofcourse if you disable your rules).
Ralph, to be clear, we decided to use that technique "from then on". We did not adapt our ongoing projects, so there were no transitions... (We even have old applications with maintenance contract -from the early ASP.NET days-, that have data access right in the code behind :S)
Bill,
The classic example of rules that I've seen is something like POSTALCODERULE --- but the reality is that just how many different tables should there be in the databas that have postal code columns? I would suggest just one, and if that's the case, then using a constraint would likely be better an rule.
We've also adopted the goal of trying to put all business rules in the middle tier, no stored procs or business triggers. So far so good.
Yes, the postal code rule is a pretty common as it’s easy to explain and relate to. But a business rule can be applied to more complex columns such as a part number, or other designator that’s encoded to permit grouping of like parts. Since the part number is used in many places, having a single place to update the rule is very handy and far safer. If we missed changing a constraint, we’re pooched. We’ve also seen rules applied to choice columns that don’t justify the overhead of a PK/FK. For example, a marriage status can be “Single”, “Divorced”, “Separated”, “Depressed”, “Civil Union” etc. A rule could easily deal with this. Sure, a constraint would work here, but there are situations where a global rule is safer and easier to work with.