Rules, Active Databases, and Distributing Logic
Discussions about system architecture often reveal a dichotomy of belief about the best use of a database.
The minimalist approach is to use a database as a passive container or simply a persistent store for data. The minimalist does not use a database to apply schemas, type checking, or data integrity constraints when storing data. If there are rules about data (for example, who can change it), they are enforced in application code.
In the minimalist context, database design is a byproduct of the design of an application (or service) and its specific data requirements, objects, and classes. The design is application-specific and not driven by requirements to integrate data across disparate applications, services, and web clients.
A different school of thought is that a database should provide more functionality than simply being a passive data store. These designers believe that a database should be extensible, adaptive to application requirements, and an enforcer of restrictions on data. In this context, the intent of a database design is to create a repository of data and related logic for use by multiple clients, including scripts, applications, and services.
Unlike a passive data store, an active database exhibits behavior and an extensible database embeds logic. The embedded logic can be in the form of triggers, stored procedures, user-defined functions, and plug-in code. Fully featured database platforms exploit plug-ins for custom handling of data, just as a web browser is extensible with Adobe Flash Player for playing video. Browser plug-ins are usually for data presentation purposes, such as playing video. Database plug-ins are usually for data manipulation and analysis.
The embedded logic enables an active database to play a role in event-driven architecture. An active database provides centralized reactive semantics for behavior that's shared across applications, services, and disparate programs and scripts.
Capabilities built into the database engine alleviate having to implement comparable logic in application code and scripts, such as enforcing constraints and rules about data.
Enterprise Modeling, Application Modeling
Enterprise data architecture has become a topic of great interest, with architects looking at the overall data structure of an organization. Enterprise data architecture can be expressed as a set of rules that describe an organization's data and validation requirements. This permits cross-system mapping from an enterprise data model to various data sources.
Data architects, data modelers, and developers responsible for multiple systems prefer the strategy of disparate programs and scripts using a shared database; having uniform rules and mappings for data being a primary reason.
Because the minimalist approach takes a narrower view, it typically results in applications, services, and scripts using disparate data sources. When new systems require data from multiple sources, this can require an added investment in integration software — either application integration, data integration, or both.
We build software using algorithms and rules, such as business rules and rules about data. The active database can play a role as a rule enforcer.
When developers talk about business rules, they are talking about processing rules in computer systems. The focus is automation — encoding policy, legal restriction, and other rules to guide the execution of programs and scripts.
"Employees must wash hands before returning to work" is not an example of a business rule that lends itself to automation. Others do, such as:
- Platinum-level customers receive free shipping.
- Purchase orders for more than $500,000 require approval of the CFO or CEO.
- All sensors in test cells must be operational and online before testing turbines.
- All workers must complete training before their names are added to the access list for the red badge zone.
Distributing Logic with Active Databases
Deciding where to place logic in distributed applications is not always a slam-dunk for a system architect. When embarking on service-oriented architecture (SOA) projects, it's not uncommon for organizations to invest in rules engines that operate on middle-tier servers. A fully featured database is an effective complement to middle-tier rules engines, giving the system architect complete flexibility about the implementation of data and business rules.
When using an adaptive, active database, rules and restrictions on data can take several forms. If you are using an SQL platform, you can use SQL DDL statements to define constraints, such as referential integrity constraints and permissible ranges for data values.
SQL statements such as
CREATE TRIGGER, CREATE RULE, CREATE TYPE, CREATE FUNCTION and
CREATE PROCEDURE provide the capability to adapt a database to both data and processing requirements.
CREATE RULE statement, for example, provides the capability of specifying alternative actions to take when inserting, updating, or deleting from tables. One use might be to specify an alternative update for a salary when the employee is a resident of California:
create rule cal-pay-1 as on update to EMP.salary where EMP.state = "CA" do update CAEMP ( ... ) where ...
SQL platforms, such as Oracle Database, IBM DB2, IBM Informix, and Sybase ASE, support different types of plug-ins, including Java in the database. Microsoft SQL Server offers a Common Language Runtime (CLR) that supports the installation of plug-ins. This enables database developers to program .NET assemblies using a variety of languages. Stay tuned for a future look at database plug-ins.