Desktop applications typically maintain their own copies of data because having the data close at hand is generally more efficient than dragging it across a network. The downside is data that’s replicated, out of sync, and very difficult to re-integrate.

The hallmark of Frontline’s smart database design is the encapsulation of data handling procedures. The applications store a minimal amount of data, “outsourcing” most of the processing chores to the database.  These centralized procedures are designed to respond to requests from any source, quickly perform the required actions, and return the output in as small a package as possible.  The result

The Smart Database

is enhanced performance, greater utility of shared procedures, improved data integrity, and overall lower development costs. 

For more information on smart database design concepts,

please click on the following links:

Data-driven design,

Metadata,

Data vs Process,

Stored procedures

A Smart Database Case Study

ClassifyIt, Frontline’s classification design framework, was developed in Access. Its first application was for an import classification product that was intended for the web. The web-application programmer converted ClassifyIt’s Access databases to MySQL, plus made some design changes

along the way, rendering the resulting data structures incompatible. As the administrative functions were still performed in Access, we were forced to maintain two versions of the database. We wrote an ETL (Extract, Transform, Load) program to bridge them, but the databases quickly got of sync. We ended up spending more time synchronizing data than building the web app.

The solution was to decouple the databases from Access and migrate them to SQL Server^®.  This involved converting large chunks of the Access data manipulation code (macros and Visual Basic) into SQL stored procedures. But the result was a smart database that serviced the admin functions in Access as well as the java-based web programs. The resulting benefits have been enormous.  While the Access user-interface wasn’t changed a bit, the program code was greatly streamlined, reliability is rock solid, performance has improved by an order of magnitude, we can bolt on almost any front-end and, best of all, no more synchronization problems!