Where Do We Put Business Rules?
I received a number of replies regarding this question. Frankly, I’m surprised with the consistency of the responses. The overwhelming response to this point has been, “Put your business logic in the database.” Primarily, the recommendations are to put the logic into Referential Integrity, metadata enforced by stored procedures, or logic in the stored procedures themselves.
Here are some of the responses…don’t worry if your submissions isn’t posted today…there were too many for a single newsletter.
Paul Writes:
The greatest long term cost of software is maintaining business rules. I agree that coding business rules in T-SQL in a mix of stored procedures and triggers is a poor design. The greatest problem with that design is that developers have falsely concluded that the T-SQL mess of business rules is the only data oriented solution, so the over-react and say all business rules should be in the app layer. While coding all business rules in a single location is a slight improvement, the rules are still hand-coded and require maintenance.
Because business rules change in reality, the best place for business rules is in meta-date where the business rules can be changes in minutes rather than months. I know from experience that handling the business rules deep in the database , resolved by well-indexed joins, performs very well. I realize this is requires more data schema design than most are capable of, or willing to do. However, when done correctly, the result is an incredibly agile and elegant application. Truly the very best place for business rules is in the data.
Andy Writes:
To unfortunately paraphrase our deputy prime-minister, “I’m with Jeffrey”. I think though that you can extend the concept a little further.
There are business rules. Rules that must never be broken. These are constraints on the data and the final port of call should be the stored procedures (and only stored procedures, not in-line SQL shoved into a business layer or mashed up with LinQ) that ensure that they are read or written to the database correctly. Security should also play a big part in this process – don’t ask for what you don’t need and don’t send back what you’re not allowed to see. By all means cache regularly used data on whatever client (by which I mean consumer of the data) but make sure that a suitable mechanism exists to maintain it’s timeliness.
Then there is business logic. Local requirements that may be different for different parts of the company, different levels of security or simply different ideas and concepts of how to get the job done. They will have their own constraints, but they remain more flexible that those that sit within the database. You have more freedom to work with this data but ultimately it still has to conform to the single point of truth.
A well structured database implementing the range of features that exist – foreign keys, constraints, defaults and so on will hugely reduce the amount of rubbish that gets written to the database and makes maintaining it much easier than having to roll out new desktop clients or deploy new webservers etc.
If business needs reporting then don’t give them unfettered access to the database, wrap up what they need to see in read only views, export it to a reporting database or build some cubes. Don’t let random entities within the organization start to “own” the database by becoming too reliant on it being in a specific format. How many local access databases do you want to have to support ?
So by all means build business logic into a middle tier if you need scalability (databases do scale pretty well as well) but do ensure that the data that it works with is of the best quality that you can deliver.
I did some work 10 years ago with a 4GL product called Versata, or Vision Jade at the time. This tool was built around the concept that the business rules were the hub of application design. For example, a business rule could be defined as, "The total amount of a purchase order is equal to the sum of the cost of all line items, plus calculated tax." From a set of rules such as this, Versata generated your database, stored procedures enforcing the rules, and a data access layer.
What set the design apart was the fact that you not only designed the database schema and relationships, but you also designed the data domain, and implementations to enforce the domain.
One thing I find interesting is that they modified the product in later releases, moving some of the business rules out of the database and into a Business Logic Server. One of the benefits of this design change was the ability to support multiple database engines.
Not many businesses change database platforms often…but, as we have seen software vendors will not always support hardware you already own. Sometime you may have to move to a different data engine to extend your hardware investment. How much does this risk change your implementation?
Thanks for the many comments…I’ll have more to post next week. I have had a strong response from those finding it effective to use the database to enforce your business rules. I’d like to hear from those of you who either work with a data engine that does not provide business rules capabilities, such as a NoSQL engine, or have successfully implemented your rules in another layer.
Send your comments or experiences to btaylor@sswug.org.
Cheers,
Ben
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.
[Watch the Show]
$$SWYNK$$
Featured Article(s)
Reporting Services for the DBA (Part 1 of 3)
Take proactive DBA work to the next level. Put your administration queries to work in SSRS and have the answers to the hard questions at your finger tips. Identify, log, and summarize critical data sources on server performance and health. Learn how to plug in to historic data from the jobs, perform data, and app logs so you can analyze and trend over time. Provide easy access to important real time and semi-historic data from the DMVs. Provide access to data that was previously off limits to management, developers, and Jr. DBAs. You will also learn how to set up access to critical real-time performance indicators through the Web, e-mail, and mobile devices. All demos will be done on SQL Server 2008. Most will be compatible with SQL Server 2005.
Featured White Paper(s)
Enterprise Content Management
The goal of an enterprise content management (ECM) system is to seamlessly connect a company’s business processes, knowledge … (read more)