 |
|
|
|
|
|
For some reason application developers and DBAs often seem to be on opposite sides of the table.
It may be because the personality types of those who are successful in one of these jobs is different
from those who do well at the other. Perhaps it is a lack of understanding of the other job. Sometimes
it is because they tend to work with different tools and languages (or perhaps different subsets of
the same language) and therefore have a hard time finding a common basis for communication.
And in some cases, unfortunately, it is because they wrestle for control of parts of the application
development process.
One of the places that this dichotomy, an attitude of “us versus them,” seems to arise is in a decision
of where certain business rules should be established and enforced. Application developers and
DBAs generally agree on the need for business rules to protect the integrity of the application’s
critical data. The DBA will frequently want to move business rules into the database where the
DBA can see them documented, while the application developer may feel more comfortable coding
the rules directly within the application code.
In general, integrity checks should be made in the database whenever possible. This ensures that
data entering the database from any program (or ad hoc tool) will conform to the desired rules. It
may also be appropriate to define some validation rules within application programs to facilitate
immediate feedback to the user. The challenge is to ensure that rules defined in two places always
agree with each other. At the very end of this chapter, we’ll come back to this challenge and suggest
a way to exploit database-defined rules and minimize the program maintenance required when a
rule has to be changed.
Database Integrity Features
One of the primary advantages provided by a database management system is a set of capabilities
for ensuring the integrity of the data it stores. Some of these capabilities are basic, while some are
much more advanced. This chapter explores some of the ways that Oracle can assist the developer
in providing automatic validation of data when it is entered into the database, as well as ensure
that the data integrity rules are maintained throughout the life of the data within the database.Data Typing
Data typing is not generally considered as a database integrity feature. However, starting a discussion
about this topic may help bridge the common gap between developers and DBAs. Every developer is
aware of the capability of data typing to help limit data errors within their programs. By declaring a
particular variable as a numeric data type, they will immediately be able to raise an error if a user tries
to enter a nonnumeric string into the field.
Just like the programming language, the database can enforce such fundamental limitations on the data that
is entered. When the database column definition is of a DATE data type, Oracle will not allow inappropriate
data to be entered into that column. By taking his or her record and field declarations from the data dictionary’s
declared data typing, the developer is able to closely coordinate the acceptance and subsequent
storage of the data. Similarly, the defined length of program variables, screen display lengths, and database
column lengths allow for smooth movement of data throughout the system.
Imagine the additional work that would be required in developing a program if all of the data were
allowed to be entered as unrestricted character strings but had to be individually edited, character by
character, at each step in every program so that it won’t error out at the last step when it is actually
submitted to the database for storage. What a nightmare that would be!
The more sophisticated capabilities of ensuring data integrity also should be considered as tools (just
like data typing) that benefit both the developer and the DBA. Both should work together to determine
the appropriate business rules and define them. By being proactive in defining business rules in ways
that allow the database to enforce them, the application developer can avoid surrendering control and,
as you will see, make his or her subsequent programming efforts easier and more manageable.
There are several primary reasons for implementing business rules in the database where possible. First,
the centralized rule ensures that violating data can’t “sneak in” a back door. This becomes a practical
matter for the application developer when multiple entry and edit programs have to be created. Perhaps
a batch program needs to bulk load data; a Web-based data entry form needs to insert individual rows;
finally an edit and correction routine needs to be provided for customer service to modify existing data.
These three entry points may, in some cases, be developed in three different languages (for instance, C,
Visual Basic, and Java). Every business rule needs to be replicated to all three environments. If the rule
ever has to be modified in the future, the maintenance staff (who may not be the original authors) will
have to be sure to make the change in three places. Any missed entry point to the system or any differences
in logic (especially in complex logic) may allow corruption of the database and be very difficult to
diagnose and correct. Any new system input (perhaps a periodic transfer of data from another database)
will need to have the same logic implemented yet another time, perhaps in another programming language,
such as PL/SQL.
In addition to reducing the maintenance overhead, implementing business rules inside the database can
also be more efficient. If your program has to call the database to verify that submitted data agrees with
data values already existing in several reference tables, each round-trip query to the database is time your
program has to wait. If, on the other hand, your program just has to insert the row and the database can
evaluate and validate the data values your program will be more efficient. In the rare (we hope!) cases
that an error is encountered, the database will pass you back the transaction and specific details of the
error so that you can provide that feedback to the user. Even in this worst case, the number of trips to the
database is the same as in the first case; but in the expected case of no errors, you will have saved multiple
validation queries to the database.This is not to imply that a data entry program should not do any validation of the entered data—quite
the contrary. Some tests, such as validating month values, will never change and will not be a maintenance
issue with your program. Verifying a zip code, on the other hand is much more dynamic and would
always need to make a database call. There is no valid reason for assuming that business rules can (or
should) be evaluated at just the application level or the database level. For immediate user feedback, it is
frequently necessary to perform validation or place limits on input at the application level. For database
integrity reasons, it is also appropriate to perform the same check within the database.
As you’ll see later in this chapter, your program can even use the database-defined constraints to
dynamically validate data at time of data entry through edits or dynamic drop-down lists without the
need to modify the program code to accommodate data changes.
Constraints
This chapter is devoted to the ability of the database to declare a large variety of business rules as part of
the definition of the data. Just like data typing’s ability to restrict which characters are allowed in a numeric
field, these constraints can restrict the allowable values of data entered into the database. The key concept
of this facility is that these are declarative constraints, which means they are nonprocedural. For simple
business rules, this declarative form is easier to “code,” easier to understand, and easier to maintain.
Additionally, declarative constraints are self-documenting. The concise syntax of defining these constraints
requires no additional commentary to interpret. Procedural code to perform the same validation might
require both additional documentation and subsequent maintenance. Storing the constraint in the
database as part of the data definition means that you can see the business rule by issuing an SQL query
against the data dictionary, as introduced in Chapter 6, “The Oracle Data Dictionary.”
One additional advantage of using declarative constraints is related to performance. Constraint checking
is often more efficient than performing the same validation within the application. By evaluating declarative
constraints within the database the Oracle optimizer has been tuned, whenever possible, to perform these
checks efficiently. The communication with the database necessary to insert, update, or delete a row is the
only network communication necessary. If a validation in the program needs to check data from the database,
that will generally require an additional call and network round-trip or context switch.
After quickly comparing another option for implementing integrity rules in the database, you’ll explore
the topic of declarative constraints in more depth.SapereOnline |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
