SelecTViews
WIth Stephen Wynkoop
All the news of the day that counts is provided here.. also, find out about normalization, what types of skills might be good for specialization and upcoming learning events…We keep you informed and up to speed on tips tricks and more.
What is a business rule really?
Now we’re really getting down to the real crux of the issue. A lot of responses have been walking around different aspects of what we call a business rule, and how they need to be validated.
For example, it makes sense to validate, for an application written for the United States of America, that you may validate the State or Zip Code for data being entered. It makes sense that this requirement is validated in the database through referential integrity. It also makes sense that this same data is validated in the user interface through a pick list, or some other control.
The issue, then, is that business rules are often validated at multiple points. This kind of example has been acknowledged a number of times, and the recommendation was that the definition of this business rule be stored in a single location, the database, and be shared up to the other layers.
However, there are a lot of other kinds of business rules, sometimes fleshed out in complicated formulas. I have worked on systems where those rules were implemented in the database and in business logic layers. I have experienced that placing those kinds of rules in a business logic layer is more extensible, and scalable. Even then, I try very hard to drive the rules through data rather as much as possible.
Michael writes from this perspective:
The consensus response is par for this discussion and very a DBA oriented view. I’m here to keep you honest!
As some readers pointed out, the definition of ‘business rule’ is very vague. What most DBAs may consider a business rule, I consider ‘Data Logic’, which is appropriate for the Data Tier or Data Layer. For me, Business Rules == Business Logic (BL). And in my domain (healthcare), BL is complex.
Across the industry in client/server architectures, the absence of a ‘middle tier’ forced BL into the Data Tier as the only vehicle for shared access. T-SQL was co-opted as a poor man’s programming language and database performance, long term TCO and business agility suffered greatly.
As a consultant, I’ve worked in many different shops. To a one, the people actually writing BL were Devs, not DBAs. This greatly deepens the problem, because as we all know Devs can’t write good T-SQL, nor should they have to. Having this complex BL in the DB becomes a significant pain point, since Devs can’t efficiently or effectively debug through the DB boundary.
There is a reason that n-Tier architecture has gained general industry acceptance as the preferred pattern for building modern systems.
However….
I am in no way advocating the end of a sProcs. That would make no sense. Data Logic is necessary, important and extremely efficient.
A little Policy goes a long way…
In one of my current situations, I am overseeing the evolution of a large ISV’s core portfolio from client/server to n-Tier (SOA). I have worked with our DBAs, looking at the mountain of sProc BL they have amassed over the last decade and discussed pro/cons and pain points.
Out of this came guidance that in my mind achieves a great inclusive, balance; “queries migrate to the DB, modification sProcs migrate to code.” This guidance is applied on a case-by-case basis.
Rationale…
Queries Migrate to the DB:
During analysis, it became very clear that the primary pain point for our DBAs were heavy, poorly written sProc or dynamic SQL queries. Some of which could bring the DB and the system to its knees.
So to leverage the proficiencies of the Data Tier, queries generally migrate toward the DB. As much as possible, BL is first applied in code to produce params, then a more focused sProc does it magic and returns the reduced result.
Modification migrates to Code:
Modification sProcs, however, were a different story. Through analysis, they were noted as not being a significant performance pain point, but had become overly complicated due to the BL they attempted to express.
So to leverage the flexibility and agility of BL in code, these sProcs are refactored into SOA code and one or more ‘atomic’ sProcs that perform necessary Data Logic, but nothing else. Transactions are also made as ‘atomic’ as possible.
This approach has proven to be very effective in making both sides happy (and more productive) by solving each side’s primary pain points.
Thank you for all your courteous responses. It’s nice to be able to share your experience without editing. I also appreciate the experience from multiple viewpoints. In any software system there are many different problems to solve and tools with which to work.
Let me encourage you to explore the options before making a commitment to one pattern or another. All options have pros and cons. All options have success and failures. Applying the wrong pattern, or not understanding how to use a pattern correctly, is more often the cause of a failure, than the pattern itself.
Daniel Shares His Experience Wtih Blob Data
My approach has been to identify the data that is IN THE CONTEXT of the blob that is needed in conjunction. Information that would be used for select sort or filter. The items that you want to index to GET AT your blob to be specific. These I store in a separate table in a separate file group. This mitigates the fragmentation that may occur in the database due to Blob entries and deletion. I am not a guru on what happens when you open the can that is the physical file of the database but from my understandings this can become an issue. When I need my blobs I have sufficient handles on the data to pull it in and filter it down to control the amount of data I am dealing with in the blobs.
Tomorrow we’ll be moving on to new topics. If you still have input you’d like to share about Business Rules, feel free to send them in. I’d be glad to include your thoughts as well. Send your insight to btaylor@sswug.org.
SQL Trivia – The Case Statement
I never got back to this topic…sorry.
The CASE statement was introduced in SQL Server 6.0 somewhere around 1996. Before that there were no intuitive ways to write an inline switch in SQL Server. Oracle had a function named dCode; but there was nothing matching ANSI standards.
What did we do in those days? We had to use the CHARINDEX function and test for the existence of desired options as being found within a string.
For example…
DECLARE @TestCode CHAR(1) = ‘B’
— Using Case Statement
SELECT
CASE @TestCode
WHEN ‘A’ THEN ‘APPLES’
WHEN ‘B’ THEN ‘BANANNAS’
WHEN ‘C’ THEN ‘CHERRIES’
ELSE ‘MIXED FRUIT’
END
— Using CHARINDEX to emulate Case Statement
SELECT SUBSTRING(‘APPLES BANANNAS CHERRIES MIXED FRUIT’, CHARINDEX(@TestCode, ‘ABCZ’) * 12 – 11, 12)
As you can see, it was really nice when Microsoft released the CASE statement. I didn’t emulate the case statement fully with the ELSE option. But, you get the picture.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
SQL Server 2008 DBCC Optimization Tips (Part 2)
In this article, you can find some helpful SQL Server 2008 DBCC performance tuning and optimization tips.
Featured White Paper(s)
Sharepoint Adoption
Written by AvePoint
Microsoft SharePoint is quickly becoming the platform of … (read more)