Contents
Through the years we have moved from desktop, to client server, to 3-tier, to n-tier, to service orientation. In the process though many things have changed, but many old habits have remained unchecked. Often this resistance to change is habitual. However many a times it is procedural. This article discusses what we are doing wrong, and possible solutions.
What I present here is one way of building n-tier systems from a design and architect view. This article does not focus on code. There are many ways to accomplish n-tier systems, this is only one of them. I hope you will find some good advice, practices, and patterns using this method.
While this article may recommend several departures from the "standard practices", everything in this article is inline with Microsoft Patterns and Practices as described in Designing Data Tier Components and Passing Data through Tiers and other documents.
Even if you don't decide to adopt every practice put forth here, you should be able to pick up and adopt at least some of them.
Ask any developer where the business logic should go and the likely answer would be: "In the business layer of course."
Ask the same developer where the business logic is in their company and they will answer again: "In the business layer of course."
It should go without stating that certainly this is where the business logic should go, in the business layer. However not just some of the business logic, but all of the business logic should go in the business layer. After reading this article, many developers would realize that what they thought was true for their systems, was actually not.
These terms are often intermixed, but for the purpose of this article I will refer to them as defined here.
A physical tier defined by a physical server, or a group of physical servers that perform the same function as each other but exist to extend capacity.
A section of the system that is contained within its own process or deployment unit. Multiple layers may co exist on one tier, but can easily be moved to another tier if some sort of remoting capabilities are used.
In many desktop applications, business logic is contained within one tier with all other layers. Because there is no need to separate the layers, the layers are typically intermixed and with no definable boundaries.
In a client server system there are two tiers, thus forcing at least two layers to be implemented. In the early days the server was simply viewed as a remote database and the division was seen as application (client) and storage (server). Typically all the business logic remained in the client, intermixed with other layers such as the user interface.
It did not take very long to realize that the network bandwidth could be reduced and logic centralized to reduce constant redeployment needs of the client by moving much of the business logic out of the client. As there were only two layers, the only place to move the business logic to was the server. The server architecturally was a well suited place in a client server system, but the database as a platform was a poor choice. Databases were designed for storage and retrieval and their extensibility was designed around such needs. Database stored procedure languages were designed for basic data transformation to supplement what could not be done in SQL. Stored procedure languages were designed to execute very quickly and not for complex business logic.
But it was the better of the two choices so business logic was moved into stored procedures. In fact I would argue that business logic was shoe horned (smashed in, made to fit) into stored procedures as a matter of pragmatism. In a two tier world, it was not perfect but was an improvement.
As problems of the client server approach became apparent, 3 tier designs became popular. The biggest and the most pressing problem at that time was one of connection count. While some databases today can handle thousands of simultaneous connections, in the 1990's most databases began to become heavily stressed at around 500 connections. Servers were often licensed per client connection. These factors made it desirable to reduce the number of connections to the database.
Connection pooling became popular, however to implement connection pooling in a system with many distinct clients, a third tier needed to be inserted between the client and the server. This middle tier became known as, the "middle tier". In many cases the middle tier existed just to coordinate the connection pools, while in other cases the business logic began to move to the middle tier because development languages (C++, VB, Delphi, Java) that were better suited than stored procedure languages could be used. Soon it became evident that the middle tier was the best place to put the business logic.
A middle tier also provided support for client connections with lower bandwidth because direct database connections typically required high speed low latency network connections.
Before I proceed further, let's exactly define what business logic is. While presenting this at conferences and to companies I have become aware of the fact that not everyone agrees to what business logic actually is, and in many cases it's not even been well thought out what business logic is and what it is not.
The database server is a storage layer. Databases are designed to store, retrieve, and update data as quickly and efficiently as possible. This functionality is often referred to as CRUD (Create, Retrieve, Update, Delete). Certainly some databases are CRUD, but that is another topic.
Databases are designed to be very fast and efficient with CRUD operations. They are not designed to format telephone numbers, calculate optimum usage and peak periods of utility usage, determine geographic destinations and routings of shipments, and so on. Yet, I have seen all of these cases, and even more complex ones implemented mostly or even wholly inside stored procedures.
Yet it's not only the complex cases. Let's consider a simple case, and one that is often not regarded even to be a business logic. The case is that of Delete Customer. In nearly every system that I have seen deleting of a customer is handled exclusively by a stored procedure. In deleting a customer however there are many business logic decisions to be taken. Can the customer be deleted? What are the processes that must be performed before and after? What are safeguards that must be checked first? From which tables the records are to be deleted, or updated as a consequence?
The database should not have any knowledge of what a customer is, but only of the elements that are used to store a customer. The database should not be able to relate which tables should store a customer object and it should treat the tables independently with reference to a customer object. The database's job is to store rows in tables that represent the customer. Apart from the referential integrity constraints, data types, null ability, and indexes that make data retrieval more expedient, the database should have no functional knowledge of what exactly constitutes a customer in the business layer.
Stored procedures if they exist should only operate on one table, with the exception of stored procedures that join tables using SQL SELECTs for the purpose of returning data. In this case, stored procedures are acting as views. Views and stored procedures should also be used for basic tabulation of values, but solely to facilitate faster and more efficient data retrieval and updates by the business layer.
Even in many companies who take pride in their latest design and techniques, and the ones who rave that all their business logic is in the business layer, one quick review of their database instantly turns up delete customer, add customer, deactivate customer, suspend customer, and others not just for customer but for many other business objects.
I often see stored procedures that perform something like this:
sp_DeleteCustomer(x)
Select row in customer table, is Locked field
If true then throw error
Sum total of customer billing table
If balance > 0 then throw error
Delete rows in customer billing table (A detail table)
if Customer table Created field older than one year then
Insert row in survey table
Delete row in customer table
Many a times some of the business logic is moved out to the business layer.
Business Layer (C#, etc)
Select row in customer table, is Locked field
If true then throw error.
Sum total of customer billing table
If balance > 0 then throw error.
if Customer table Created field older than one year then
Insert row in survey table
Call sp_DeleteCustomer
sp_DeleteCustomer(x)
Delete rows in customer billing table (A detail table)
Delete row in customer table
In this case, some of the business logic has been moved, but not all. Which tables are affected is business logic as well. The database should not have any knowledge of which tables constitute a customer on the business level. These are better served in the business layer. For each of the three operations, the business layer issues a SQL or calls three separate stored procedures to execute the functionality in the last sp_DeleteCustomer.
Moving all the business logic to the business layer, we have:
Business Layer (C#, etc)
Select row in customer table, is Locked field
If true then throw error.
Sum total of customer billing table
If balance > 0 then throw error.
if Customer table Created field older than one year then
Insert row in survey table
Call sp_DeleteCustomer
Delete rows in customer billing table (A detail table)
Delete row in customer table
The delete rows can use a stored procedure if they delete just one table, however with modern databases using query plan caching, there is little performance benefit. In addition, the SQL sent by such systems is very simple since it only works on a single table and thus has such a simplistic plan that there is almost no need for optimization to be performed. In fact some databases suffer more from having too many stored procedures loaded, than they do from executing simplified SQL statements.
By moving even the table modifications to the business layer, the following advantages are gained:
- The system becomes database portable with less effort as each of these stored procedures need not be ported to each database.
- Future modifications are easier as all the logic is contained in one place, not two.
- Debugging is easier as the logic is not split between two places.
- Other business logic cannot "slip" into stored procedures because it's "easier".
While this requires three successive calls to the database instead of one, your business tier should be connected to the database on a separate high speed segment, like a 1 gigabit segment. Sending 300 bytes versus 100 bytes will not make a tangible difference. Most databases also support batch commits of SQL and the three statements can be sent to the database in one batch, reducing network calls. A data access layer should be used for issuing such SQL, instead of embedding SQL statements into the code.
Some DBAs and even developers may not accept this level of integration and insist on keeping such batch updates in stored procedures. This is a choice you need to make and depends largely on your database as well as your priorities. Because nearly all modern databases now optimize even submitted SQL based on a query plan cache, there is little performance difference in most cases, yet there are definite technical reasons not to put the logic in stored procedures. If you choose to keep such batched updates in stored procedures you should be very careful not to let other business logic slip into stored procedures and keep your stored procedures functionality purely to CRUD operations, without embedded conditional operations and other business logic.
Let's consider yet another item that I've found to be quite divisive with developers over whether or not it is business logic. I shall demonstrate why I believe it is business logic and not user interface or storage. The item is that of non-simplistic formatting. The example I will use is a telephone number.
Each country has its own format for displaying telephone numbers in a visually pleasing way. In most countries there is even more than one common way. Some examples are as follows:
Cyprus:
+357 (25) 66 00 34
+357 (25) 660 034
+357 25 660 034
+357 2566 0034
Germany:
+49 211 123456
+49 211 1234-0
North America (USA, Canada, some parts of Caribbean)
+1 (423) 235-2423
+1-423-235-2423
Russia:
+7 (812) 438-46-02
+7 (812) 438-4602
Germany even has an official regulation specifying the format called DIN 5008.
Of course, the country codes are usually not included locally. But let's assume our system is international so we will store and display the country codes as well. For each country we will pick one format to display the numbers.
Considerations for the formatting of phone numbers:
- Input will come in a variety of formats.
- Each country has its own unique ways of displaying the numbers.
- Some countries' formats are not simple and change depending on the first few digits.
- The first few digits (usually the region/area code) are not always a fixed number of digits. In the Russian example, 812 is the area code for the City of St. Petersburg. 095 is Moscow, but parts of Siberia and other regions are 4 digits (3952). This causes the total length of the phone number and the format to change depending on the region code.
- With the introduction of new portability laws, new mobile providers, European Union integration, phone system upgrades and more phone number formats and length change fairly frequently on a global basis. In recent years Cyprus has changed their area code scheme twice to accommodate first a more expandable system, and then multiple mobile telephony carriers. With hundreds of countries world wide, you can expect changes to occur on a regular basis.
Typically what is done on the input is that all the non numeric characters are stripped so that the phone numbers become like this:
Phone: 35725660034
Sometimes the country code is separated and stored in a separate field as follows:
PhoneCountry: 357
PhoneLocal: 25660034
This might seem simple, but this brings up yet another business logic issue. Not all country codes are of the same number of digits. Country codes range from 1 to 3 digits in length.
Often the parsing of input (if the country code is separated) and the display logic is implemented in the client as the client is written using a traditional language that is well suited. The problem is that the client requires a large amount of data to determine the length of country codes, and requires a redistribution of the client each time the display routine needs to be updated.
Sometimes the formatting is done in stored procedures. The problem with this approach is that stored procedure languages are ill suited to this type of logic and often leads to bugs as well as inefficient processing of the actual logic.
More often the telephone number is stored twice. It is stored first in a raw format so that it can be indexed and easily searched, and a second time formatted for easy display. In addition to the problems presented previously, additional problems of duplicated data and updating to compensate for new formats also exist.
In some extreme cases, and amazingly frequently enough, the phone number is stored in whatever format the input came in. The problem with this is obvious; the phone number cannot be easily located, indexed, or used for sorting.
It is important that although it is formatting, it's not user interface, and although the urge for any centralization often ends up in the database, this is clearly business logic. Implementing formatting in the business layer eliminates duplicate data, and allows for implementation using a development language rather than shoe horning it into a data language.
Certain batch updates are many times faster when implemented in stored procedures. Most of the situations can be handled directly by SQL, but a few types of batch updates require a looping behaviour that if implemented in the business layer would create thousands of SQL statements. In these rare cases, a stored procedure should be used even if it requires some business logic to be implemented in the stored procedure. Special care should be taken to implement as little as possible in this stored procedure.
I touch on this subject again later in this article.
In a client server system the business logic is most often split between the client and the server.
Actual percentages vary by application and enterprise, the previous example is a good coverage of client server applications. Much of the business logic has been implemented in stored procedures and views in an attempt to centralize the business logic. However many business rules cannot be easily implemented in SQL or stored procedures, or are faster to execute on the client as it relates to the user interface. Because of these opposing factors, the business rules are split between the client and the server.
For a variety of reasons which I will cover later in the obstacles topic, when n-tier systems are built the situation is often made worse with regards to consolidation of business logic. Instead of consolidation, the business logic becomes even more fragmented.
Of course each system is different depending on how the business logic is distributed among the layers, but one thing is common to them. The business logic is now distributed among three layers instead of two. I will present some common scenarios next.
A common distribution of business logic in an n-tier system is as follows:
In such cases, the business layer contains no business rules. It is not a true business layer, but merely an XML (or other streaming format) formatter and mapper for database result sets. While some advantages can be gained such as database connection pooling, database independence, and a degree of separation with the database, this is not a true business logic layer. It is more of an artificial physical layer without a logical layer.
Another common scenario is as follows:
Typically some of the rules from the application are moved to the business layer, while what was in the database mostly remained there.
When the business layer in such designs is reused the business rules that remain in the application must be duplicated. This defeats one of the primary goals of implementing a business layer.
It is even possible for calling applications to violate business rules by not implementing them, or simply ignoring them. With a true business layer this is not possible.
Instead the business layer should contain all of the business rules.
Such a design has the following advantages:
- All the business logic exists in a single location and can be easily verified, debugged, and modified.
- A true development language can be used to implement business rules. Such a language is both more flexible and more suited to such business rules rather than the SQL and stored procedures.
- The database becomes a storage layer and can focus on efficiently retrieving and storing data without any constraints related to business logic implementations or the presentation layer.
The above scenario is the goal; however some duplication, especially for validation purposes should exist in the client as well. Such rules should be reinforced by the business layer. Furthermore in some systems for reasons of extreme performance benefits such as batch updates may cause an overriding exception and should be placed in the database. So a more realistic approach appears as follows. Note that 100% still exists in the business layer and that the minimal pieces that exist in other layers are actually duplications and exist solely for the purpose of performance or disabling user interface fields according to selections, etc.
While moving to middle tier there is always an urge to "Let's just put this piece in a stored procedure". Then "another" and "another". And soon you are in the same situation you were before with not much having been changed.
Stored procedures should be used to execute SQL and return result sets in databases that optimize stored procedures better than views, such as SQL server. But stored procedures should not do anything other than join data when returning data. For updating data it should do exactly and only that and should not interpret the data in any way.
There are cases where for drastic performance reasons some item should be moved to a stored procedure. However these cases are in fact rare and should be an exception, not a rule. Each exception should be reviewed and approved and not simply done at the will of a developer or database admin.
It might sound odd that buying more hardware can be cheaper. But when you add additional middle tier servers, very little software is needed beyond the operating system.
While increasing database server capacity the costs are significant for two reasons:
- Database server hardware is typically of a higher caliber than the middle tier servers and is more expensive.
- Databases are very often licensed per CPU and adding additional CPU's or servers is costly in terms of license fees. License fees can range from $5,000 per CPU to $40,000 per CPU.
By moving the logic to the middle tier, you can significantly decrease the load on the database server and prevent premature scaling of your database.
In addition to the costs, upgrading a middle tier is usually easier than upgrading the database.
Databases have inherent limits on how much they can be scaled by simply adding more hardware. At some point other techniques such as partitioning, clustering, replication and other techniques must be used. But none of these techniques are simplistic, and all involve significant investments in hardware, migration, and/or impact on existing systems.
Middle tier servers however are easy to scale. Once a load distributor is installed, it's simply a matter of adding a new server to expand the capacity.
Let's consider the factors I have just discussed using the following diagram. The shades in the bars above show the direction or magnitude of their caption relative to the tiers in the diagram. Cost per unit increases as we move from client, to middle tier, to the database. I have used the word unit to refer to processor or server, depending on the configuration.
When the same results are charted with relative values, they can be compared easily:
I have not placed numbers on the graph because the numbers are heavily dependent on network configurations, processor power, and other factors unique to each enterprise. Each measure also uses a different unit of measurement. What I have presented here is a general relationship of magnitude of each measurement, overlaid with each other to show relationships. This clearly shows that the middle tier has a capacity to scale, and also being cheaper and easier to do than the database.
If significant portions of the business logic are implemented in the database, you will need a bigger database. The scenario is as follows:
By moving the logic to the middle tier, you can drastically reduce the load on the database. The actual numbers here are for demonstration purpose and will vary with each system, but they should help you understand the point. While the following diagram requires more hardware as a whole, the overall cost of the system is cheaper and easier to deploy. It is much easier, and cheaper to grow the middle tiers.
Let's revisit one item of the previous graph:
What is the single bottleneck in this system capacity-wise? Which of these tiers have a discernible limit as to how large it can grow? It's obvious that it's the database. Everything funnels downhill into the database.
Thus by moving the processing into the middle tier we are able to stay farther away from the boundaries of the database layer.
There are several obstacles in moving to a consolidated design, and not just the obvious ones like coding differently.
There is a saying, "old habits die hard". This is applicable even in a team. In a team you need to convince not just yourself, but the majority of your team members.
Many companies have long standing security policies that specify security must be controlled in the database and using stored procedures as views do not offer enough control. Changing company security policies to evolve into a world of n-tier can often prove to be difficult, if not impossible.
With .NET security and future offerings from Microsoft there is more focus on enterprise security at the middle tier level than ever before, however many corporations still focus on the database and they are either unaware of the changes or are unwilling to change.
This topic is a touchy one. However touchy, it is something that must be said. Whether you are a DBA (Database Administrator) or a developer please do not interpret what I am about to say as being stereotypical or true of all DBA's. However this is quite prevalent and quite common. If you are a DBA who does not fall into this profile, then bravo! You are a Database President not a Database Lord.
A DBA with a working system is often reluctant to make any substantial changes because it could break their system. Many enterprises have one DBA and many DAs (Database assistants). The DBA is the king of his domain and wields final decision on everything related to database. Only management will ever override the DBA, and management due to their inability to judge technical DB issues will always defer to the DBA.
Most of the DBAs often have very little knowledge of the changing needs of the n-tier system, or simply do not care. To them, any tier is just another client and everything to them is a client server model. They care only about running their database and do make some compromises for the developers but only if it does not cause them any grief.
DBAs do not typically move between companies as quickly as developers and most of them have been presiding over an enterprise's database for the past 10, or even 20 years. The database is a very important thing to them and they are unwilling to make any compromises. They have established their kingdom and are reluctant to let their control go. Getting such DBAs to let go off a level of security and implementation can be a major struggle and will require the involvement of the management.
Other DBAs are not so picky, and will get along with anything that they find to be within reason. But in many enterprises, especially large ones there are dozens or hundreds of developers, and just one, or a few DBAs and the DBAs sit at the top of the enterprise chain of command.
Most of the tools available today are either not conducive or do not promote the implementation of business logic. Many tools simply focus on scalability, connection pooling and database independence and yet do not address the needs of business logic implementation well.
I have found it very useful to have the system architect do regular reviews and flag improper placements of business logic. The sooner it is caught, the easier and less costly it is to fix. If you do not have a person designated as the master architect then the developers of the team can do policing of each other. If something is found to be in an improper place, that developer can alert the team and then the team leader.
Educating the DA (Database Assistants) is very important. DAs have been implementing business logic for so long, that it will be difficult for them to be able to separate what is business logic from what is storage. DAs would typically do whatever is needed of them, usually following the instructions of the DBA.
DAs still remain involved. They still perform JOINs, optimize SQL, and maintain the database. They should also monitor the SQL coming from the middle tier and monitor the database performance. The DAs will also continue to perform database design.
There is often resistance from the management, although this is one of the easier obstacles to overcome rather than the difficult ones. The management will not care if your job is easier, but they will care about the costs, development time, business benefits, and be sure to throw in a bunch of current buzz words as well.
The major obstacle in changing the management will likely be resistance from the DBA. So sell management solid, and let them take care of the DBA.
The ideas in this article are patterns and practices that I have been using for nearly 10 years. Of course they are constantly being refined and updated to take advantage of the newer technologies and adapt to the changing needs.
During my work I go through a lot of papers written by "experts". Most of these papers unfortunately are written by developers who are great at coming up with theories and telling people how they should do things yet never putting their own practices to work. Other papers are written by experienced developers who do not have a wide scope and their practices are quite domain specific, yet they present themselves as having wide spread knowledge and cure for all applications. When developers read such papers, they assume that there is only one way to solve every problem. Developers need to be more open minded and understand that there is always more than one way to solve a problem and that such papers are experiences of other users that should be used as guidelines, not gospel.
I had to mention all these because it's rare to come across something that is truly excellent and that does not fall into one of these traps. One of the best papers I have read in recent years was written in August 2002 and is one of Microsoft's Patterns and Practices papers. It coincides very well and is a good companion paper to what I've presented here and in my other articles.
Please refer to Designing data tier components and passing data through tiers.
I have also written a related article:
Changing directions in a large enterprise is often very political and risky. From a developers perspective it's much easier to lay low and let others do the fighting. I doubt many developers will put a full stop to their long used practices. Through this article I wish to give you some ideas to modify your existing processes or at least evaluate certain decisions closer that otherwise might pass with little consideration.
The approaches described here are best applied in new systems, or when rebuilding a whole or partial system. With existing systems, it's usually best to leave things alone unless there is some other overriding factor causing a redesign.
