SelecTViews
With Stephen Wynkoop
Ryan Adams joins the show today, also information about disaster recovery and planning. Additional information on recent news, the SQL Server tip of the day and much more.
[Watch the Show]
Where Do Business Rules Belong? Reader consensus Says In the Database
Thanks to all of you for your input on this subject. Many have written with appreciation.
Chris says:
I just wanted to say thanks for asking the question and posting the response. I very much agree and it’s good to hear from others that it’s ‘okay’ to put your business rules next to your data in the database. Business rules are not easily confined to one physical layer – they need to be executed in the UI, middle-tier and, why not the database? It’s less a question of where and more of a question of how…
The consensus for the best location implementing business rules to this point has been, “Put them in the Database.”
Rules should enforce referential integrity, data domain and any other sort of business logic. The tools recommended to this point have been, Schema (Referential Integrity), triggers and stored procedures. All the heavy lifting is done by the data engine itself.
All of the comments, with a single exception, have referenced only two options. The business logic belongs only in the database, or the business logic belongs in the client.
Claude has a little different perspective:
There are different rules that could be considered business rules. There are also different types of applications that would warrant doing things in different ways. Let me explain a little more…
There are rules that relate to data integrity and those most certainly belong in the database. Foreign keys and other constraints have their home on the SQL server.
But to create an application with a responsive UI I also need to be able to have validation in the application. Should I try and save data to a table and see if it errors or should I know right away that there is a problem before I try and save the data so the user doesn’t waste time entering and trying to save before knowing something is wrong? In my world (.net client, SQL Server backend, and some WCF), I would not have a job if I did that.
Does the rule that a user must have a name belong in my business logic within my application or do I solely rely of the “NOT NULL” constraint in the database? How about a rule that the name must >= 3 characters long? Those are business rules too.
Should my entry form even have an enabled “Save” button if the data is invalid?
In the above case I am forced to have the business rule that a name can’t be blank in 2 places, the database, and my application. I agree with your comment about the rules being in metadata where they can be easily changed.
I this day and age with the ever evolving options and complexity of systems we always struggle with where to implement certain features, like validation. A large majority of the business rules I referenced in my initial comments were related to the business rules that validate data.
In a .net application where we treat everything as an object we want to make sure that the data in those objects is valid, it must meet all the business rules.
How can we have these kinds of rules in one place so they are easily changed without requiring the application to be modified and redeployed?
Data driving applications is a great way to accomplish this. One thing we have started to look at is to include the rules with the database. Yes, I said that. But, in the meta data of the database. More specifically in the extended descriptions of tables and columns.
This way thing are controlled and changed in one place. The object generation code within our applications will read this data at startup and it will be used to validate the data and to enforce the rules.
Other complexities come in with the advent of Microsoft’s Entity Framework which abstracts the application from the database and creates different issues.
Applications are moving from existing in one homogeneous component to being distributed and disconnected. The User Interface doesn’t need to know anything about what its displaying or where the data comes from. The business logic layer doesn’t care about the User Interface or the data server. And the Server doesn’t care about either of them. The all just do what their supposed to do.
As developers our challenge is to incorporate changes in a planed way to keep things working with or without a new front end, backend, or middle tier.
The bottom line is that we can’t look at a solution as a application and a database server, but we need to look at the “systems” that make up the whole solution and they are much more involved today. For every need there are multiple solutions and for every solution there are pros and cons… Fortunately, or unfortunately sometimes, it all comes down to who is responsible for maintaining the solution and what the cost is.
What is the ideal? That depends on what makes up your world!
Here are some more reader comments from a traditional perspective…
Randy:
I wish this were not an issue. I feel like a roman soldier watching the Visigoths prepare for battle.
Since the dawn of FrontPage MS has strived to create the ultimate WYSIWYG application development tool. So that completely ignorant people can build applications. They’re limited success with visual studio has, and is, a disease in the IT community. I am not saying that VS is a bad tool, or that all VS users are ignorant, just that there are now a significant percentage of VS users who are not competent and the quality of applications has harmed the reputation of computers and the people who work on them.
“We have removed the requirement for developers to maintain a fundamental understanding of databases and HTML.”
MS on Visual Studio 2007.
Bad code will always perform badly, The Things I have seen!!
Where Do Business Rules Belong?
- In one central location through which all access to data must pass.
- Close to the data and native to the storage tool so;
- When rules are applied the communication from the medium is consistent.
- Security is maintained regardless of the number of clients or technologies used to connect.
- Encapsulation allows for better application longevity through Extensibility.
SQL Server stored procedures were created because we have already learned this lesson the hard way.
Where stored procedures are not available, then a Middle Tier is required.
If you do not have the resources or permissions to create and deploy a middle tier, then your project is too small to be considered for this discussion.
Cade:
There are rules at all different levels of complexity. When someone says business rules, I ask "what kind?". There are very low level rules like a piece of data cannot be missing (NOT NULL), or that an order must have a customer (foreign key), or a calculation (like an area or volume – perhaps a persisted calculated column in a table or a derived column in a view), or something in a trigger or a stored procedure. Or perhaps it is so high-level that the database cannot ever realistically know (some kind of distributed SOA operation which requires third-party services to sign off on something).
I strongly recommend that a database protect its perimeter, even within an overall system. Just like any modular system, there need to be some control of the coupling. The surface area exposed by the database should be cohesive. Beyond that, the next layer of the system, whether it is a business layer or a reporting engine or ad hoc queries by data analysts, needs to be able to have some guarantees – that’s what the database is providing, both data, metadata and contracts about what can and cannot happen in that data. Anything not enforced at the database level is one more thing that can potentially be violated if you don’t follow the exposed interface of the next higher level. So that no one ever says "the database allows this but it should never happen"
I advocate putting rules in the place which is as close to the data being governed as possible. That way, in all layers above that point, the contract about the nature of that data is preserved. This generally means that in some systems a very high proportion of the "business rules" are in the database. But these are what you might call "database guarantees".
John:
As long as the business rules are in one place then I’m happy – when they are mixed across the application code and the QSL code it makes it very hard to gain an overall picture. At least if the business logic is in stored procs in a 3rd party application you can see it.
There are other processes to consider when making your decision. I’ll be introducing them to the discussion soon. For example, if you put your business logic in a middle tier, how does that impact your ETL tools?
Do you have thoughts or experiences that help clarify the question. Share them with us by sending an Email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
A Basic Introduction to Coding DB2 SQL for Performance (Part 3)
This article series is intended to give the basics of good SQL programming to application developers. Consider implementing these guidelines to facilitate better SQL performance.
Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
When you’re looking for a reliable tool to diagnose SQL Server issues, Perfmon is often the answer. No more wasting time trac… (read more)