Here we describe the part of the application that concerns us first. We'll tell you about the job it has to do as well as show you a diagram of the relevant parts of the database. (See Figure 3-2.)
Figure 3-2. The application modifies the Horses table. The three other tables will serve as lookup tables only.
The purpose of this part of the application is to maintain information about horses that have run in races or that are at least trained to do so. Part of the work of maintaining information about these horses includes adding new horses to the database as well as modifying or deleting data about horses already in the system. You'll find as you read along that there's a small bonus to be won as well: you'll learn at least a few Swedish words.
This application must follow a small set of rules—eight of them to be exact:
These are all the rules we need to observe when we create our sample application. In Chapter 4, "Implementing the Rules," we'll decide how best to implement each rule. But in general, we need to consider implementing a given rule in any one of three places:
Let's consider the principal advantages and liabilities of each of these locations before, in the next chapter, we decide where to implement each rule.
In the two-tier world, you have a choice between implementing a rule in the client application or in the database. In many cases, the rules are put in the client application simply because the developer feels more comfortable doing that than creating stored procedures and triggers. Such a decision is often justified. After all, creating a solid stored procedure or a set-oriented trigger is much more difficult than creating the corresponding code in a full-fledged programming language such as Visual Basic. We especially mention set-oriented in connection with triggers because structured query language (SQL) is a set-oriented language, whereas the mind-set of most programmers is record-oriented. Thus, many developers tend to create record-oriented triggers where they should have created set-oriented ones. A record-oriented trigger might render a good result when activated by a record-oriented SQL statement; when activated by a set-oriented SQL statement, such a trigger might severely damage the integrity of the database. Not surprisingly, many developers prefer to implement rules in Visual Basic rather than in stored procedures and triggers.
There are, however, several problems with such a strategy. Here are a few of them:
For these reasons and others, client-based rules are appropriate only in noncritical small applications and databases with relatively few users. For more critical applications and for databases with larger numbers of users, developers should implement all the rules in the database.
This strategy makes it possible to use the two-tier client/server model for serious applications. In Sweden, a number of successful mission-critical applications use this model, especially in the banking and insurance industries.
This strategy has several advantages. Here are a few of them:
There are, however, a few disadvantages to this approach:
For all these reasons, the two-tier client/server model hasn't been as successful as it was meant to be. Many organizations have refrained from moving mission-critical applications to two-tier platforms.
Now, in addition to implementing rules as user or data services, you can implement rules in yet another way. You can put some or all of your business and application rules in the business services tier of a three-tier (or n-tier) solution.
If you combine such a strategy with a sound security system, allowing only components in the business services tier to modify the database, you can enjoy the combination of the best parts of the two-tier world:
Remember, under this scheme no user has direct access to the database, at least not for modification purposes. The database accepts modifications only if they come through a component in the business services tier. The user can be permitted to call on that component for help but not to change the database directly.
For a young client/server developer, this kind of security might sound weird, but a veteran programmer will feel very much at home with it. It is, in fact, a step backward since the old host-based programs gave users access to code, not to the database directly.
It is, however, a very useful step backward. In the two-tier client/server world, a user can do a partial update of the database, failing to make other modifications that should follow the first one. In the new three-tier world, a component can be programmed always to perform the full update. If part of such an update fails, the system will automatically roll back any part completed. Either the full update comes through or none of it. That's guaranteed.
You can express this in another way: the three-tier model allows you to build rule-based systems, something that was much harder to do successfully in the two-tier world.
The business services tier is the main place for business rules, no question about it. But is it the only place?
Right now there's a tremendous focus on the business services tier as the place for rule implementation. Technical writers, speakers, and presenters even call this tier the business rules tier rather than the business services tier. From the questions put to us by developers during our seminars and courses, we know that many developers initially believe that the business services tier is the only tier for business rules. From the same sources, we know that many developers initially believe that they should refrain from using stored procedures in the database, now that they put their rules in the business services tier.
We have two fundamental points of disagreement with this position. We think you can advantageously put your rules elsewhere, as we'll show in the next chapter, when we explain how to implement our rules. And we think stored procedures can help you a lot, even if you have implemented every business rule in the business services tier.
For one thing, using stored procedures is good for your application's performance. This is true anyway if your database is a Microsoft SQL Server. You can count on a performance boost of about 40 percent when using stored procedures as compared with sending ordinary SQL statements from the business services tier to the database.4
For another thing, stored procedures help the scalability of your database application. As performance increases, clients don't have to stay as long in the database; they simply use resources for shorter periods of time. Since resources are freed earlier, scalability increases.
Our good friend and former Microsoft employee Peter Hussey, who was on the SQL Server team in Redmond almost from the start of Microsoft SQL Server, constantly reminds us of the following fact: If raw performance is your main consideration, you should go for a two-tier solution using stored procedures for all your data access.
Now then, if the business services tier isn't the only place for rules, where should some of them go? Answer: they should go into the data services tier.
Normally, we don't think that complex rules that have to be programmed should reside in the database. You'll be much better off putting them in the business services tier. In other words, complex stored procedures or triggers might be bad for you. It's much better to program them in a real programming language such as Visual Basic or Microsoft Visual C++.
If a rule can easily be declared in the database, and the same rule would have to be programmed if you put it in a class in the business services tier, what should you do with it? In our opinion, the answer is easy: declare it in the database.
Here's a small list of situations for which you should consider implementing rules in the database:
Please agree with us that this is a short list. And it should be! The business services tier earns its alias—the business rules tier. Most rules should go into it.
As with two-tier applications, you can still implement business rules in the user services tier. But please don't even consider it!
Saying that, we know that many developers will not fully agree with us. Allow us to make a few comments about this statement:
We know some of you might be asking, "Where should we put our validation code? We want two things, and they're difficult to combine. First, we want to be sure that the user's actions are always validated. This indicates that the validation code should be as close to the database as possible; we understand that the validation code should be a last resort, impossible for any client to bypass. Second, we want the user to be warned of problems as early as possible. This indicates that we should put the validation code as close to the user as possible. What should we do?"
It all comes down to priorities. Which is more important?
The answer should be obvious. If the data is important enough to validate in the first place, the validation code should have a place that makes it impossible to bypass, no matter which application is used. In a two-tier application, this indicates the use of triggers or stored procedures. In a three-tier or n-tier application, it implies rules in the business tier or in the database.
Anyway, we should be able to agree that the client program isn't the place to validate what the client or the client program does. And yet the second priority in the preceding list is quite valid. The user should be serviced as well as possible. He or she shouldn't have to enter a lot of data only to find out, a while later, that some of it couldn't be saved because it wasn't valid.
So should we use redundant validation? For example, should we use a trigger to be really sure the data is valid as well as a Visual Basic validation method in the client to give early warning?
Redundant validation is one way to solve the problem. But chances are you'll be in trouble when rules change. You'll have to change the rules in the database as well as in the client code. You'll also have to make sure that you change the rule correctly in both places. Therefore, you should avoid redundant validation.
What you should strive for is rather to help the user enter valid information. Give him or her choices that can't go wrong. But don't rely on that help. Make sure that the server side checks the client side. Whatever the client does, the server has to make certain that all the data coming in is valid and that the rules are adhered to.
So let's move to the next chapter, which includes a description of how to combine server validation with client help in our sample application.