Channels ▼

Ken North

Dr. Dobb's Bloggers

Rules, Active Databases, and Distributing Logic

October 14, 2011

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.

Rules

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:

  1. Platinum-level customers receive free shipping.

  2. Purchase orders for more than $500,000 require approval of the CFO or CEO.

  3. All sensors in test cells must be operational and online before testing turbines.

  4. 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.

The PostgreSQL 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 ... 

Besides SQL statements, there are other solutions for adapting a database to meet the requirements of your services and applications. With an open-source NoSQL database, you have the option of writing code in a programming or scripting language. With CouchDB, for example, you can write JavaScript code to generate views. MongoDB provides bindings that enable the use of one of several languages for writing adaptive code, including C++, Java, JavaScript, Perl, PHP, Python, and Ruby.

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.

Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 


Video