Kailasnath Awati

Subscribe to Kailasnath Awati: eMailAlertsEmail Alerts
Get Kailasnath Awati: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Related Topics: ColdFusion on Ulitzer, Java Developer Magazine

CFDJ: Article

Some Thoughts on The Design of CF Data Input Applications

Some Thoughts on The Design of CF Data Input Applications

ColdFusion is a powerful addition to the toolbox of Internet application developers. However, it's only one of several used in constructing a complete application. Others include relational databases with their procedural extensions, client- and server-side Java and JavaScript, HTML, and related technologies such as CSS.

Web developers need to have a good technical mastery of each of these tools, much of which can be learned from books and courses. What's harder to learn is the art of integrating these technologies - the hows and wheres of implementing application functionality. In this article we present some of our thoughts on this issue, with particular reference to ColdFusion-based data input applications.

Our firm has used ColdFusion to develop several financial data input applications that enable users to input income statements, capital expenditure reporting and budgeting, and sales reports over the Web. These applications model complicated business processes using input forms with large numbers of input elements, complex data validation schemes, and considerable back-end processing on an enterprise-strength RDBMS (DB/2, Oracle, SQL Server, etc.). Our discussion here focuses on a few recurring design issues that cropped up when we were developing these "high-volume" data input applications. The topics we cover are conveniently classified as follows:

  1. Data input and validation
  2. Data processing
  3. Data security

Basic Landscape: Three-Tier Architecture
The starting point is the basic three-tier architecture commonly found in Internet business applications - the usual browser-web server-database paradigm. What makes this architecture a challenge is that each tier is actually composed of several building blocks (e.g., client/server-side Java and JavaScript) that you may or may not want to integrate in your application (see Figure 1).

For concreteness we assume that the client is equipped with an up-to-date mainstream browser (IE 5.5), the middle tier includes a top-line web server along with the ColdFusion server, and the back end has Oracle 8i (DB/2, Sybase, or Informix will meet the requirements just as well). Although simple on the surface (see Figure 2), this basic architecture is versatile and scalable. Keep in mind that this three-tier architecture considerably complicates the decision process regarding where to place elements of the application by multiplying the choices offered to the developer (for example, data validation can be done on the client, the ColdFusion server, or the database server).

Data Input and Validation Issues
To CFFORM or Not to CFFORM

One of the more common questions facing a ColdFusion developer is whether to use ColdFusion's enhanced form (CF-FORM) or the standard HTML form. At first sight, the choice is obviously CFFORM, since it offers the following benefits:

  1. A range of novel form elements including CFGRID, CFSLIDER, CFTEXTINPUT, and CFTREE. These offer the developer new functionality not available in standard HTML.
  2. Enhancements to standard HTML elements such as INPUT (CFINPUT) and SELECT (CFSELECT). For the developer the most significant enhancement is built-in client- and server-side validation.

However, as we'll argue below, for high volume data input applications it's much better to use simple HTML form elements.

  1. The novel form elements mentioned above are Java controls. A good network connection then becomes a prerequisite because some of the applets may require considerable time to download. Keep in mind that in many countries fast and stable Internet access is not easily available, even to corporate users. Be sure to take such users into account before deciding to go with these elements. Java applets are just fine, but if you can achieve exactly the same effect with simple HTML (albeit dynamically generated), then you should definitely do so. An example would be to replace the use of CFTREE to represent hierarchies with nested HTML lists. Response times are much better with the HTML lists, and the results can be formatted (using stylesheets) to look very elegant. We've used this in several projects in which a number of users from Asia and South America were experiencing horrendous download times with the CFTREE control.
  2. When client-side validation offered by CFINPUT and CFSELECT is used, Cold-Fusion generates separate JavaScript validation routines for each input element. If your form contains a large number of input elements, this limitation can quickly become prohibitive because of the copious volumes of code generated. For this reason we prefer to custom write our client validation code and avoid the use of CFFORM elements altogether.

Layout and Presentation
With the advent of cascading stylesheets (CSS), ColdFusion developers now have a good level of control on input form layout and presentation. We urge you to explore some of the things stylesheets make possible. For example, listing 1 shows how a hyperlink can masquerade as a button using CSS (why you might want to do this is another matter!). Note that the demo has been tested only on Microsoft Internet Explorer 4.0+, which brings us to a word of caution: browsers vary considerably in support of CSS, so use the more arcane features only after checking that they're supported by your target browsers.

Another issue, often overlooked, is the level of control that CSS offers in the look and feel of form elements. It allows you to control font, width, size, color, and border. Here's a customized input box for numerical data input that illustrates the use of some of these CSS attributes:

<input type = "Text"
size="15"
maxlength="12"
style="font-size: xx-small; font-family: Arial; text-align: right;">
Note that CSS gives you an easy way to right-align the number instead of living with the default left alignment. Further, restrictions on data length can be enforced without a single line of JavaScript through use of the (non-CSS) "maxlength" attribute.

A frequently encountered situation for financial data input applications is that you have a complex grid of form elements that need to be entered. A possible business context is when yearly budgeting of a chart of accounts is done on a monthly basis: 12 months running on the top of the page along with say 50 accounts running down the left side of the page make for 600 input form fields. How you implement this in ColdFusion depends on how your database tables are organized. Are the months represented as separate columns or as a single column? If it's the former, a simple CFQUERY (ordered by account) followed by a CFOUTPUT will do the job. However, in the latter, your query will have to order the data by account and month, then use the "group" attribute of CFOUTPUT to ensure the form elements are placed correctly.

Staying with the above example, the end users now ask you to enable the up/down keys on their keyboards to allow for rapid navigation within the grid. The catch here is that the form elements don't have any simple way of being accessed with a two-dimensional index. It's only one form and each element is numbered sequentially. The two-dimensional grid is an illusion of the HTML layout. The only way to do this is to use JavaScript and implement a counting scheme after catching the "onkeydown" event. The number of the form element is coded in the element name. The code is presented in listing 2 (Microsoft Internet Explorer 4+ only). In the more general case when the number of rows varies depending on data coming from the database, the ColdFusion server can be made to generate the form names dynamically so you can easily implement the counting scheme. This nontrivial piece of presentation logic is handled strictly by the web browser.

Data Validation (Client Side)
There are several languages available for client-side validation (JavaScript and VBScript, for example). However, as Java-Script (or, more properly, ECMAScript) is an accepted standard, we'll stick with it in this discussion.

Despite standardization efforts, there are considerable differences in implementation for different browsers. Further, with new browser versions coming up for release periodically, there's a virtual zoo of JavaScript implementations out there. Our only advice is to take the lowest common denominator wherever possible, and to use browser detection codes if you need to use vendor-specific features. Check out www.javascripts.com for browser-detection code.

The nicest feature of client-side Java-Script validation is that it prevents user aggravation by avoiding unnecessary trips to the server. However, the downside is that clients can disable these features, so you shouldn't count on them being there. Consequently, you should always implement a second line of defense - data validation on the server (see below). Al-though client-side validation can be used to perform complex validations, it's best suited to simple checks, for example, on data types or formats. This is, in fact, what the JavaScript generated by CFFORM elements does. However, for reasons mentioned above, it's best to avoid using these.

It's difficult to give any general rules regarding the implementation of client-side validation routines for high-volume data input applications, as each case is unique. Further, it's usually best to see such routines in action, so we'll say no more about this here and refer our readers to www.orafusion.com/js.htm for a look at some Java-Script demos taken from our projects.

Data Validation (Server Side)
Server-side validation offers the most flexible and surefire method of checking user inputs. These checks can be carried out on the ColdFusion server using CFML constructs or, better yet, on the database via data type definitions, check constraints, and database triggers. Any of these three methods can be used to return database exceptions that can be handled using ColdFusion's error handling tags (CFTRY and CFCATCH). We believe that all server-side validation should be done on the database instead of the ColdFusion server. ColdFusion should serve merely as a conduit for transporting information (data, exceptions, messages) between the database and the client, or for carrying out presentation-related procedural manipulations. The ColdFusion server is definitely not the place to do any complex manipulation of data (validation included).

There are essentially three methods of data validation on the database server:

  1. Data Type Validation
    Data type validation is one of the most basic server-side validations available. ANSI has gone through considerable pain to define some basic SQL data types that are supported by all major enterprise RDBMSs. Whenever a table is created, each column must be assigned a unique data type. The database server will then throw an error whenever a column data type is violated. Data types by themselves tell only part of the story. The other part is told by the data type comparison semantics that define how the RDBMS allows you to compare data, even across data types in some cases. Finally, but also important in the context of data types, are user-defined data types. Because most RDBMSs now also offer object extensions, it's possible to define your own data type using the CREATE-TYPE statement. Using such basic validation you can guarantee that, for example, a product code is numerical, not alphanumerical. This guarantee is inherent to your data structure and cannot be violated, regardless of the application accessing your database. Very powerful indeed!
  2. Check Constraints
    Check constraints are an extremely effective and largely underutilized method of data validation. These constraints are built into table definitions and are consequently very efficient. They allow the developer to perform relatively complicated checks right where the data resides. Here are a few simple examples.
    If you're storing the month for a given record in numerical form in a table, you can use the constraint:
    CHECK (MONTH IN [1,2,3,4,5,6,7,8,9,10,11,12]).
    Similarly if you want to guarantee that the year is within a given range, say 1980 to 2010, use the check constraint:
    CHECK (YEAR BETWEEN 1980 AND 2010).
    Clearly these checks can and should also be performed on the client via JavaScript, because they're simple (quickly implemented) and can save two sets of round-trips, thus improving performance (browser-web server and web server-database server). The advantage of having the check in your database is that you're guaranteed that all applications accessing the data must conform to the business rule, not just your particular ColdFusion application.
  3. Triggers
    A trigger is a block of procedural code executed when a certain event that modifies data in a table is performed (INSERT, UPDATE, or DELETE). For example, if you're entering sales data for a given product in a table, you might have a trigger to first check that the product being referenced is still active. If the product is not active, you may want to raise an exception and let the users know they've somehow managed to select the wrong product. This again is done right where the data resides. On the other hand, if you filter out the active products at the level of your ColdFusion server, you're prone to data integrity violations if someone accesses the data through other tools or applications.

Until very recently these blocks of code had to be written in a procedural extension to SQL. These extensions are RDBMS specific and proprietary, examples being PL/SQL or Transact SQL. Some exciting new developments have been taking place in the database industry (supported by corresponding standards) that should open the eyes of corporate web developers. An important one is that most major database companies (Microsoft being the exception!) allow Java code to be executed directly within the database. A trigger is only one example of where this can be done. This means you can apply any Java programming skills to developing RDBMS code blocks that implement business process logic without having to use proprietary RDBMS languages. If that's not enough, a new interface called SQLJ has been accepted as part of the Java standard (ANSI & ISO); this allows you to basically write inline SQL statements in your Java procedures, making Java and SQL appear as if they were originally conceived together. Not bad!

You can reference several introductory articles on Java and databases in Java Developer's Journal. We'll also be talking a bit more about these technologies in an upcoming series of articles on Oracle and ColdFusion.

Data Processing
Once your data has been entered in the HTML form you'll want to process it. A typical example from one of our applications is that users input data (monetary values) in their local country currency and the application then converts these values into several standard corporate currencies for display and comparison purposes. There are many ways of performing such tasks, but our experience has been that, by far, the best is to place such processing of data directly on the back-end tier using stored procedures. The implementation of Java in the mainstream RDBMSs now opens up the possibility of writing code that's independent of the specific database being used. This could make Java (with SQLJ) the language of choice for implementing stored procedures.

Database procedures are generally accessed via the <CFSTOREDPROC> tag. If the procedure runs quickly (say a few seconds), we usually call the procedure directly, wait for the output, and then return any feedback directly to the user. When the procedure requires more than just a couple of seconds we usually invoke an intermediate procedure, its only function being to toss the main procedure on a job queue within the database. Details of job queue implementation depend on the specific DBMS used; we'll talk about how this is done in Oracle in one of the articles in our upcoming series. After the queued procedure runs to completion, the end user is sent feedback via an e-mail message automatically generated using <CFMAIL> and the ColdFusion scheduler. This mail usually contains the duration of the calculations, time of completion, and a summarized view of the results (for example, total income and total expenses when the user was entering income statement information).

Data Security
Finally we'd like to discuss some efficient ways of ensuring data security, a major issue in the corporate world, especially when an application is to be placed on the Internet. As we'll see, this is best done in the database.

First, each user should gain entry to the application via a database login and password. Further, the database should be located on a separate machine (normally behind the corporate firewall) from the ColdFusion/Web server. This ensures a degree of security beyond that gained by access through operating system usernames/passwords.

Second, once users are validated against the database, we need to ensure that they have controlled access to the data (i.e., they should be able to see only what they're allowed to see). Consider a simple scenario with product managers and products of a large consumer goods corporation. Each product is assigned to only one product manager who enters the yearly sales figure for the product through a ColdFusion application. The corporation, however, has a policy of strict confidentiality and asks that project managers can see and modify only their assigned products. The tables to model this process might look like Tables 1 and 2.

What's needed is an infrastructure that will give you row-level control on the YEARLY_PRODUCT_SALES table. You can implement the requested security policy on the ColdFusion server, but it's very cumbersome and if the product manager accesses the database through another tool such as Excel, all data will be visible.

The solution to this problem is to simply place a view on top of the table (Oracle syntax) YEARLY_PRODUCT_SALES (see Table 3).

The simple join guarantees that the product managers have access to only the assigned products. Your ColdFusion application should never access the table directly, only the view. In fact, your users need not have any knowledge about the underlying data table. This mechanism is effective and easy to implement. Once created in the database you never need worry about it again when developing your application, as long as you access all data through the view.

Here's a peek at the future: beginning with version 8i, Oracle introduced the concept of CONTEXTS, which allow you to do the same thing as we displayed above but within a much more general framework. This new feature also allows you to leverage your Java knowledge in the database. Without going into the details here we definitely suggest you look at this functionality. It's another of the topics we intend to explore in our upcoming Oracle series.

Conclusion
Application development is certainly a challenge when working with high-pressure deadlines in a corporate environment. In fact, that's why a lot of developers use ColdFusion. It's important to keep in mind, though, that in a three-tier environment you also have other tools at your disposal, some of which may be more appropriate for the tasks at hand. A good case can be made for putting business-process logic in the database along with the data instead of the Web/ColdFusion server. Modern RD-BMSs are extremely powerful, versatile, and probably the most scalable piece of enterprise software available aside from operating systems. We believe this can be summarized into a simple, yet powerful, generalization: business presentation logic and preliminary data validation should be carried out as much as possible on the client whereas all business process logic should reside as closely as possible to the data itself.

This is a tug-of-war that every Internet application must confront with the ColdFusion server sitting right in the middle. This perspective has allowed us to successfully develop complex systems for large numbers of concurrent users in relatively short periods of time. The resulting applications have also remained flexible without falling victim to "Scope Creep Fatigue" (we're sure you've heard some of your users say "Just one more little change...") - the point beyond which applications turn into maintenance nightmares.

More Stories By Kailasnath Awati

Kailasnath Awati is senior consultant at Williams & Partner
Management Consulting where he manages the U.S. operations. He is an
experienced Internet/database application developer and architect as
well as an Allaire certified ColdFusion developer.

More Stories By Mario Techera

Mario Techera specializes in Internet application design and
development with relational databases. He is one of the founding
partners of Williams & Partner Management Consulting and works out of
Munich, Germany.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.