Where Do Business Rules Belong?
I received a response to a reader’s comments from yesterdays Editorial. The original reader felt that implementing business rules in a database through the use of stored procedures and/or triggers was a bad thing. The server could not scale.
Today our comment comes from Jeffrey. Jeffrey points out operational and deployment benefits of maintaining rules in your database.
Both individuals have complete opposite views. What position is right or wrong? Are they both right, but the situation determines the correct pattern? I have my own point of view (as always). But this time I’m going to hold my opinion, allowing you to share your experience and understanding.
So get into the conversation. I’d like to hear what your pattern is for implementing business rules. Share with us the pros and cons of your implementation. It will also be helpful to understand the kind of implementation you have for your software, and the tools, layers, software available to you.
Here are some examples:
- Would someone developing code on a mainframe using PL1 or cobol and Oracle find it easier to implement business rules in the Oracle engine or in the application code?
- Where would you put your business rules on a two tiered client server system?
- How about using a business objects engine such as BEA? Do you still put some business rules in your database engine?
- What about you Java gurus? Do you create a business layer with business objects and rules?
- In Dot Net, do you take the time to create a business layer, or just put your business rules in the database?
- What did you do using MySql before it supported stored procedures?
- How about our RPG friends working on the AS400 platform…where to you implement business rules?
- Clearly those using distributed database engines such as BigTable, Cassandra or hadoop are not going to put their business rules in the database engine. Where do your rules go?
I think we’ll find that one size does not fit all, and that there are different options available today that warrant consideration.
Jeffrey writes:
As a SQL Server DBA/designer for the past 18 years – (Sybase before that and Oracle before that and COBOL linked lists on a mainframe before that!!) I think that Claud (readers comment in the “TOP (10) Worst Things to Do in a Database” (May 18, 2011) (527465)) is completely missing the point when it comes to business rules. If it is a business – it has one and only one set of rules – and these rules are MUCH better kept in the database – NEVER on the client. Business rules change – slowly – but they do change – and the choice is changing them ONCE on the server – guaranteeing that the database follows these rules – or passing out the changes to maybe thousands of clients – and hoping that the changes are executed! I have worked at several places where the rules were on the client – and should a rule change – you’re now a distribution center for fixes – no longer a database!!! Did they get the fix? Did they run it BEFORE they ran the nightly updates? Business rules should apply to the entire business – and the best way to enforce them and be sure that they are ALL EXACTLY THE SAME – is to have them on the server in the database. I would MUCH rather change a rule ONCE – on the server than have to pass the rule out to 1,000 clients – and HOPE that they execute them in a timely manner!!!!
Claud also states: “I am recently worked on a project where 90+ percent of the business logic was in stored procedures. Performance on the SQL Server was poor because all processes ran as stored procedures.” Well, Claud – not really – it is not SQL Server – it is the procedures! The only reason for a poor performing SQL Server database is either a poor database design (including indexing) or poor SQL procedure coding – PERIOD!!! I once ran several SQL Server database – each with TRILLIONS of rows of data – and executing a report was usually an 8 to 12 hour job – started at night so they would complete by morning!! After implementing a few indexes and a few structural changes within the database – and I had the exact same reports running in under 15 minutes. A well written stored procedure (or well laid out tables) that encapsulates a business rule – SHOULD be easy to code and easy to process. And in over 30 years in the database business – I have yet to see a business rule that cannot be maintained within a database – without any performance hit whatsoever.
Share your experience with us. Drop me an email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
The Role of SQL Server
During an IT crisis, it is important that everyone knows their roles. Those tasked with finding the cause of the issue, also known as "detectives", are going to pay attention to the change controls and service packs, particularly about when they are installed.
Featured White Paper(s)
Storage Optimization
Microsoft SharePoint Server 2010, the latest release of Microsoft’s best-selling server product, is quickly revolutionizing … (read more)
Featured Script
Reindex all Tables in a Database
Reinedex all tables in a database without entering any table names in…. (read more)