Chapter 5, “Translating User Needs into Data Models,” explained how to build models to represent the entities involved in a database project and to study the interactions among those entities. The final kind of model described in that chapter, the relational model, has a structure that closely mimics the organization of a relational database. You can easily convert a relational model into a working relational database.

Before you do, however, you should optimize the relational model to make the final database as flexible, robust, and efficient as possible. Optimizing the model now is easier than reorganizing the database later, so it's worth taking some time to make sure you get the database design right the first time.

The first step in optimizing the database is extracting business rules. Keeping business rules separate from other database constraints and relations, at least logically, makes later changes to the database easier.

In this chapter, you will learn:

• Why business rules are important
• How to identify business rules
• How to modify a relational model to isolate business rules

After you understand business rules, you'll be able to use them to make your databases more flexible and easier to maintain.

WHAT ARE BUSINESS RULES?

Business rules describe the objects, relationships, and actions that a business finds important and worth writing down. They include rules and policies that define how a business operates and handles its day-to-day operations. They generally help a business satisfy its goals and meet its obligations.

For example, some general business rules might be:

• The nearest clerk greets customers by saying “Welcome to Cloud Nine” when they enter the store.
• Clerks ask to see a customer's ID when writing a check for more than $20 or charging more than $50. No signature is required when charging less than $25.
• Whoever unlocks the door in the morning makes the first pot of coffee (or risks mutiny).
• Save the good scotch for the landlord.

Because this is a database design book, this chapter is only concerned with the database-related business rules. Some examples of those are:

• Don't create a Customer record until the customer buys something and has an associated order.
• Customer records must include first and last names. (If Bono, Everlast, or Madonna buys something, get an autograph and use “Star” for the last name.)
• If a student doesn't enroll in at least one class, change the Status field to Inactive.
• If a salesperson sells more than 10 hot tubs in one month, award a $200 bonus.
• All Contact records must include at least one phone number or email address.
• If an order totals more than $100 before taxes and shipping, give the customer a 10 percent discount.
• If an order totals more than $50 before taxes and shipping, give the customer free shipping.
• Employees get a 1 percent discount. (Because we're a people company and we value our employees!)
• If the in-stock quantity of an inventory item drops below the number of items sold during the previous month, order more.

From a database viewpoint, business rules are constraints. Some are simple constraints such as:

• All orders must include a ContactPhoneNumber.

Simple rules such as this one map easily to the features provided by a relational database. It's easy to indicate that a field has a certain data type or that it is required (as in this case).

Other business rules may represent quite complex constraints such as:

• A student's number of course hours plus number of project hours must be between 1 and 14.

You can implement some of these more complex rules with check constraints or foreign key constraints. Recall from Chapter 2, “Relational Overview,” that check constraints include field-level constraints that apply to a single field in a table, and table-level constraints can examine more than one field in the same record.

Still other business rules are even more complex:

• An instructor must have a combination of classes, labs, and office hours totaling at least 30 contact hours with up to one-half office hour per hour of class, 1 office hour per hour of lab, and thesis supervision counts as 2 hours.

This constraint may require you to gather data from several different tables. This kind of very complex check is probably best performed by code either in a stored procedure inside the database or in external software.

All of these rules are implemented as constraints in one form or another, whether as easy database features (requiring a field), as harder database features (check constraints and foreign keys), or in code (inside or outside of the database).

IDENTIFYING KEY BUSINESS RULES

Writing down all of the business rules is worthwhile in its own right so that you can ensure that they all are implemented somehow in the database. It's also worth categorizing the business rules so that you can build them properly.

How you implement a business rule depends not only on how tricky it is to build, but also on how likely it will be to change later. If a rule is likely to change later, then you may be better off building it by using a more complicated but more flexible method.

For example, suppose you only ship orders to states where you have a warehouse and those include Wyoming, Nebraska, Colorado, and Kansas. A business rule requires that the State field in an order's shipping address must be WY, NE, CO, or KS. You can implement this as a simple field-level check constraint in the Orders table. Three minutes' work and you're a hero! No big deal.

But now suppose you open a new warehouse in Utah. To allow for this change, you'll need to edit this check constraint. This isn't the end of the world, but this change requires that you modify the structure of the database.

Now suppose the company policy changes so some warehouses are allowed to ship to certain other states. You'll need to change the database's check constraints again to allow for the change. This still isn't the end of the world, but once more you're required to change the structure of the database to accommodate a change to a business rule.

Now consider an alternative approach. Suppose instead of making this business rule a field-level check constraint on the State field, you make it a foreign key constraint. You create a ShippingStates table and fill it with the values WY, NE, CO, and KS. Then you make the Orders table's State field a foreign key referring to the new ShippingStates table. Now the Orders table will accept only records that have a State value that is listed in the ShippingStates table.

If you need to change the states that are allowed, you only need to add or remove records from the ShippingStates table. Admittedly, the difference in difficulty between this approach and the previous one is small. The previous approach required changing the database's structure, but the new approach only requires changing the data.

Not only does changing the data take a bit less effort, but it also requires less skill. This rule implemented as a check constraint might look like this:

State = 'WY' Or State = 'NE' Or State = 'CO' Or State = 'KS'

This isn't terribly difficult code, but it is code and only someone familiar with database programming will be able to make changes to it.

Data in the States table, however, is relatively easy to understand. Even your customers can add entries to this table (possibly with a few hints from you).

Placing the validation data in a separate table also allows the users to understand it more easily. Most users would be intimidated by the previous check constraint (even if they can find it inside the database), but they can easily understand a list of allowed values in a table.

To identify these key business rules, ask yourself two questions. First, how easy is it to change a rule? If a rule is very complex, it will be difficult to change without messing it up. If implementing the rule is as simple as making a field required or not in a table, then you won't lose a huge amount of time if the customer later decides that the Lumberjacks table's PreferredAxe field isn't required after all.

Second, how likely is the rule to change? If a rule is likely to change frequently, then it's probably worth some extra planning to make changing the rule easier.

Types of rules that make good candidates for extra attention include:

• Enumerated Values—For example, allowed shipping states, order statuses (Pending, Approved, Shipped), and service names (Installation, Repair, Dog Washing).
• Calculation Parameters—For example, suppose you give free shipping on orders over $50. Will you later change that to $75? $100? (Based on experience, I would say that these values are almost certain to change eventually.)
• Validity Parameters—For example, suppose full-time students must take 8 to 16 credits. Will we ever make this 12 to 16 credits? 8 to 20 credits? Suppose you require that all projects include between 2 and 5 students. Will you ever want to allow a single student to have a project? Or will you allow a bigger team if a group of friends wants to work together badly enough to bribe you with donuts and latte?
• Cross-Record and Cross-Table Checks—These kinds of checks are more complicated. For example, you might require that the date and time of a poker game be after the date the tournament started. (Although the Olympics schedules competitions before the opening ceremony. They probably use some sort of time-warp effect at international levels.)
• Generalizable Constraints—If you think you might need to apply a similar constraint later, you should think about generalizing the constraint and moving it out of the database proper. For example, suppose your buyer skipped a decimal point and ordered 100 sets of crampons (those spiky things that ice climbers wear on their boots) instead of 10. To move the excess inventory, you offer a $50 bonus to any salesperson who can sell 10 pairs in a week. That's fine, but next month you might end up with an excess inventory of ice axes. After you fire your buyer, you might want to change the incentive to give a $30 bonus to any salesperson who sells 5 ice axes. You can make these changes easier if you pull the product name or ID, number of sales, bonus amount, and duration (weekly) out into another table and then use those parameters to calculate bonuses. (And, yes, it can be hard to anticipate this sort of thing when you build the initial database. This is something more to consider when you're adding the bonuses to an existing database.)
• Very Complicated Checks—Some checks are so complex that it's just easier to move them into code, either as stored procedures within the database or in external code modules. For example, suppose you can only register for the course Predicate Calculus (in the Mathematics department) if you have already taken (and passed) Propositional Calculus (in the Mathematics department) or Logic I and Logic II (in the Philosophy department). Or if you have the instructor's permission or your advisor's. You can probably implement this as a table-level check constraint, but it might be worth thinking about moving this rule somewhere else, particularly because you might be able to generalize it to handle prerequisites for other courses.

Types of rules that are usually not worth special attention and can be just implemented directly in the database include:

• Enumerated Types with Fixed Values—Although it might make sense to move allowed values for a State field into a new table, it probably doesn't make sense to do the same for a Handedness field. Unless you're planning to start marketing to octopi and squids, Left Handed and Right Handed (and possibly Ambidextrous) are probably the only values you'll ever need for this field.
• Data Type Requirements—Requiring specific data types for a field is one of the bigger advantages to using a relational database. It hardly ever makes sense to use a very generic data type such as string because you're not sure whether the field will need to be a currency amount or a date. If you are that unsure, you probably need to study this field some more or split it into multiple fields.
• Required Values—If a GolfRound record really needs a Caddie entry (so the golfer knows who to blame for using the 3-wood on that 124-yard par 3 hole), just make it required and worry about something more complicated.
• Sanity Checks—For example, all inventory items should have a price of at least 0. You might want to allow 0 cost for loss leaders (or perhaps not), but if I ever find a store that sells a product for less than nothing (i.e., pays me), I'm going down there with a dump truck and cleaning them out. (Now that I think about it, I've bought a few products that would have been overpriced at negative amounts. I might have to think a bit harder depending on what the product is.) If the sanity checks are so broad that they'll never need to be changed, just wire them in and don't worry about them.

Somewhere in the middle ground are business rules that have never changed in the past but that you cannot swear won't change in the future. They might be easy to implement as checks within the database, but there still might be some advantage to extracting them to accommodate changes.

For example, suppose you require that Resident Advisors (RAs) have passed all of their general education requirements. It's been that way for five years, but before that the rule was different. Chances are the rule won't change again, at least not for a long time, but you never know. There has been talk about exempting RAs from the writing requirement ('cause riting ain't emportunt enuff).

In cases such as this one, you need to rely on the judgment of those who make the rules. (Then when the unexpected happens, you can blame them.)

So, write down all of the business rules you can discover. Include the domains of every field and any simple bounds checks such as Price > 0 in addition to more complicated rules.

Group the rules by how likely they are to change and how hard they would be to change. Then take a closer look at the ones that are likely to change and that will be hard to change and see if you shouldn't pull them out of the database's structure.

EXTRACTING KEY BUSINESS RULES

Now that you've identified the business rules that will be tricky to implement within the database or that might change frequently, pull them out of the database. There are a couple of standard approaches for doing that.

First, if the rule is a validation list, convert it into a foreign key constraint. Shipping to a set of specific states is the perfect example. Simply create a States table, enter the allowed states, and then make the Orders table's State field be a foreign key referring to the States table.

Second, if the rule is a fairly straightforward calculation with parameters that may change, pull the parameters out and place them in a table. For example, if you want to give salespeople who sell at least $250,000 worth of cars in a month a $5 bonus, pull the parameters $250,000 and $5 out and put them in a table. In some businesses, you might even want to pull out the duration one month.

I've written several applications that had a special Parameters table containing all sorts of oddball parameters that were used to perform calculations, check constraints, and otherwise determine the system's behavior. The table had two fields: Name and Value. To see if a salesperson should get the bonus, you would look up the record with the Name BonusSales, get the corresponding Value, and see if their sales totaled at least that much. If so, you would look up the BonusAward parameter, get its Value, and give the salesperson that big a bonus. (This approach works particularly well when program code performs the checks. When the program starts, it can load all of the parameters into a collection. Later, it can look up values in the collection without hitting the database.)

Third, if a calculation is complicated, extract it into code. That doesn't necessarily mean you need to write the code in C++, Python, C#, Ada, or the latest programming language flavor-of-the-month. Many database products can store and execute stored procedures. A stored procedure can select and iterate through records, perform calculations, make comparisons, and do just about anything that a full-fledged programming language can.

So, what's the point of moving checks into a stored procedure? Partly it's a matter of perception. Pulling the check out of the database's table structure and making it a stored procedure separates it logically from the tables. That makes it easier to divide up maintenance work on the database into structural work and programming work.

Of course, you can also build the check into code written in a traditional programming language. You might be able to invoke that code from the database, or you might use it in the project's user interface.

Finally, if you have a rule that you might want to generalize, well, you're going to have to use your judgment and imagination. For example, suppose an author of a database design book earns a 5 percent royalty on the first 5,000 copies sold, 7 percent on the next 5,000, and 10 percent on any copies after that. You could code those numbers into a stored procedure to calculate royalties but then later, when Steven Spielberg turns the book into a blockbuster movie, you better believe the author will want better terms for the sequel!

Rather than writing these values into the code, place them in a table. In this case, those values are associated with a particular record in the Books table. You might want more or less than three percentage values for different royalty points, so you'll need to pull the values into their own table (in ER diagram terms, the new table will be a weak entity with an identifying relationship to the Books table).

Figure 6.1 shows a tiny part of the relational model for this database. To find the royalty rates for a particular book, you would look up the RoyaltyRates records for that book's BookId.

Now, it will be a little more work calculating royalty payments than before (although you can still hide the details in a stored procedure if you like), but it is easy to create new royalty schedules for future books.

MULTI-TIER APPLICATIONS

A multi-tier application uses several different layers to handle different data-related tasks. The most common form of multi-tier application uses three tiers. (The tiers are also often called layers, so you'll hear talk of three-layer systems.)

The first tier (often called the user interface tier or user interface layer) is the user interface. It displays data and lets the user manipulate it. It might perform some basic data validation such as ensuring that required fields are filled in and that numeric values are actually numbers, but it doesn't implement complicated business rules.

The third tier (often called the data tier or database tier or layer) is the database. It stores the data with as few restrictions as possible. Normally, it provides basic validation (NumberOfRockets is required, MaximumTwistyness must be between 0.0 and 1.0) but it doesn't implement complicated business rules, either.

The middle tier (often called the business tier or business layer) is a service layer that moves data between the first and third tiers. This is the tier that implements all of the business rules. When the user interface tier tries to send data back to the database, the middle tier verifies that the data satisfies the business rules and either sends the data to the data tier or complains to the user interface tier. When it fetches data from the database, the middle tier may also perform calculations on the data to create derived values to forward to the user interface tier. For example, the database might store temperature in degrees Celsius and the middle tier might convert that into degrees Fahrenheit or degrees Kelvin for the user interface to display.

Figure 6.2 shows the three-tier architecture graphically.

The main goal of a multi-tier architecture is to increase flexibility. The user interface and database tiers can work relatively independently while the middle tier provides any necessary translation. For example, if the user interface changes so a particular value must be displayed differently (perhaps in a dropdown instead of in a text box), it can make that change without requiring any changes to the database. If the database must change how a value is stored (perhaps as a string Small/Medium/Large instead of as a numeric size code), the user interface doesn't need to know about it. The middle tier might need to be adjusted to handle any differences, but the first and third tiers are isolated from each other.

The middle tier also concentrates most of the business logic. The user interface and database perform basic validations, but the middle tier does all of the heavy lifting.

Another advantage of multi-tier systems is that the tiers can run on different computers. The database might run on a computer at corporate headquarters, the middle-tier libraries might run on a second computer (or even be split across two other computers), and the user interface can run on many users' computers. Or all three tiers might run on the same computer. Separating the tiers lets you shuffle them around to fit your computing environment. In a modern cloud environment, some of the pieces may run on a cloud service provider's computers and you might not even know where they are physically.

In practice, there's some benefit to placing at least some checks in the database tier so, if there's a problem in the rest of the application, the database has the final say. For example, if the user interface contains an obscure bug so customers who order more than 999 pencils on leap year day are charged $–32,767, then the database can save the day by refusing that obviously harebrained price.

There's also some value to placing basic checks in the user interface so the application doesn't need to perform unnecessary round-trips to the database. For example, it doesn't make a lot of sense to ship an entire order's data across the network to the corporate database only to have it rejected because the order is for –10 buggy whips. The user interface should be smart enough to know that customers cannot order less than zero of something.

Adding validations in both the user interface and the database requires some redundancy, but it's worth it. (Also notice that it allows the user interface developers and database programmers to do their work separately so they can work in parallel.)

Although multi-tier architecture is far outside the scope of this book, it's worth knowing a little about it so you understand that there's another benefit to extracting complex business rules from the database's table structure. Even if you implement those rules in stored procedures within the database, you still get some of the benefits of a logical separation and flexibility almost as if you had a hidden extra tier.

SUMMARY

Business rules are, quite simply, the rules that govern how a business runs. They cover everything from a list of acceptable attire for Casual Fridays to the schedule of performance bonuses.

As far as databases are concerned, business rules help define the data model. They define every field's domain (what values and ranges of values they can contain), whether fields are required, the fields' data types, and any special conditions on the fields.

Some rules are simple and unlikely to change, so they can be easily implemented by using the database's features. Other rules are complex or subject to occasional change. You can make changing those rules easier by separating them from the database's structure either physically or logically.

In this chapter, you learned how to:

• Understand business rules.
• Identify key business rules that may deserve special attention.
• Isolate key business rules physically or logically by extracting their data into tables, moving them into stored procedures, and moving them into a middle tier.

Separating business rules from the database's table structure is one way to make a database more efficient and flexible. The next chapter describes another important way to improve the database's behavior: normalization.

Before you move on to Chapter 7, however, use the following exercises to test your understanding of the material covered in this chapter. You can find the solutions to these exercises in Appendix A.