Database System Software and N-Tier Development

Introduction
Database system software consists of three main components or layers: the database, the user interface, and the process layer. Many tools are available to develop these components. One of many common basic architectures uses these technologies: SQL Server (database), VB.Net (user interface), VB.Net + SQL Server stored procedures (process layer).

Software engineers refer to this basic architecture as a 3-tier system or n-tier system (“n” is a variable number, typically 3 or more layers). Generally the 3-layer design applies to all database software. Often one or more of the three layers integrates multiple (“n”) technologies and/or steps. Desktop software and online website database applications use 3-tier systems.

Microsoft Access is probably the most widely used database system software. Any MS Access system that includes a form or report (user inteface – UI) linked to a (database) table is a basic 2-tier system. Sometimes you do not need the middle tier – the process or application layer, but usually systems have some level of processing. For example, you want to validate incoming data before saving it into the database table.

Database Layer
The database layer is perhaps the easiest to understand: it is simply where the data is stored. Again, MS Access is the most widely-used database storage system. Many other database products exist: Microsoft SQL Server, Oracle, mySQL, postgreSQL, DB2, and more. These database products are often used for both desktop and online database systems. Older desktop software often used DBase, Paradox, or FoxPro.

The most basic storage is a text file. A CSV (comma separated values) file has a separate row of text for each record. Each row is separated by a “delimiter” (often a comma), to designate where each data field begins and ends inside the row. A basic Excel spreadsheet/worksheet is essentially another way to view a CSV file. An XML file is a text file with tags to delineate data records, fields, elements, relationships, and attributes.

Typically, software systems use true database products rather than text files (or spreadsheets). Database products enable data to be stored in various tables. Tables usually have “identity keys” that enable a table to relate to other tables. This is called a relational database system (RDBS). One-to-many relationships are essential to database systems. A common example is that one sales order may include many products ordered. Database administrators use rules of normalization to organize data logically and avoid unnecesary redundacies.

User-Interface (UI) Layer
The user interface (UI) is how people interact with the database. The most basic and important example is a form, which includes data fields and one or more buttons to perform actions, especially the action of saving updates to the database. Usually software contains several forms, which users access easily via menus, toolbars, or button switchboards. These all comprise a visual design that users see on the screen, and this is called the graphic user interface (GUI).  We interact with the software using the keyboard and/or mouse (or touchpad, trackball, touchscreen, etc).

Developers need to design forms to be logical and easy to use, i.e., user-friendly and intuitive. Forms often contain various components to efficiently present data, facilitate data retrieval, and enable quick navigation from one data set to another.

The concept of list/detail is essential. Usually database software displays a summarized list of data records with an ability to click on one record to show (and edit) its detailed information. Email software is an example everyone uses. We see a list of our emails in the inbox, and then can click on one of them to get to the detail. Developers display lists using components such as a datagrid, listbox, listview, treeview, and other methods.

The detail form often uses special components for various needs. Developers need to use the best component (or tool or control) for each data field. The text box is the most common, but others include drop-down lists, radio buttons, checkboxes, calendar controls, image boxes, and others.

To have powerful, well-organizing forms, developers also use tools/components such as tabs, panes, nested lists, menus, toolbars, and others. These tools enable software to show relationships and provide quick and easy access to information and functionality. Older software tended to use many pop-up forms and sometimes a pop-up form is the best choice. However, the integratation of many features, functions, lists, and details into each form generally produces the best graphic user interface.

Historically, more tools have been available for desktop software development than online database applications. Recent technology and techniques have enabled web-based solutions to increasingly have the power of desktop solutions. Various development products are available for creating a user-interface. Microsoft’s Visual Studio is especially popular, especially because many of its tools and coding techniques apply well to both desktop and online software systems.

In addition to forms, other methods are available for the user to interact with data and systems. The most common example is a printed report. A much more interesting example is that software products are increasingly using voice recognition and sound as tools for users. Sound interaction has been available for quite a long time but still has not been incorporated widely in business applications.

Process or Application Layer
The “process layer” is often also called the “application layer”. This is the layer between the stored data and the user interface. Programmers implement business rules in this layer.

A common approach is to first validate any attempts to update data. Trying to put an invalid date in a date field will likely cause a database storage error. At the very least, the system will have erroneous data in the database. Programmers need to make sure that all data meet the correct business rules to guarantee the most accurate and acceptable data possible. Programming languages such as C# and Visual Basic are popular for this task. Many website forms use Javascript to validate form data. Often the data validation step is incorporated into the user-interface. For example, a drop-down list of acceptable values guarantees that only good data is passed to the next step.

Once the data is validated, then programming code may instruct the system to send the data to the database. The “best practice” is to call a stored procedure to handle the incoming data update request. Many database products enable developers to write stored procedures (using t-SQL language) which process incoming data. The stored procedure may update multiple tables and perform various data transactions.

After the stored procedure updates the database tables, programming code may execute additional functions, such as sending out an email or printing out a report.

The process layer must also handle errors. A multitude of reasons may cause an error somewhere in the process. Without proper error handling, the software encounters a glitch and may cease to function properly. This can be extremely confusing to the user and open the door for more problems. The code must trap errors and execute a method for resolving each error. This often includes presenting clear messages and instructions to the user. This approach guarantees stable software and a positive user experience.

Implications of N-Tier Architecture
All database systems use some form of 3-tier architecture, however, many approaches exist. Carefully choosing the right technology for each component of software is a “best practice” that produces the best software in terms of performance and scalability. For example, some developers may rely too heavily on VB code or t-SQL, when the best approach is use the best tool for the job.

Developers can “mix and match” technologies. ODBC (open database connectivity) allows many user-interface tools and process layer languages to connect to many database products. This also facilitates prototyping. For example, a developer may set up a SQL Server database but use MS Access for the front-end to quickly put together the user-interface as a demonstration version. Once the requirements are more clearly established, the developer may use Visual Studio to produce a more “heavy duty” version of the software.

Some software offers both website and desktop versions. Carefully designed architeture enables developers to more easily produce versions of the software for both platforms. Ideally the database and process layers apply to both versions, so the bulk of new work is focused on the user-interface.

The separation of software design into these tiers provides maximum flexibility, scalability, and high performance. This also enables developers to work as a team more effectively, with each team member focused on specific tasks inside the larger framework.

This entry was posted on Monday, January 5th, 2009 at 9:06 am and is filed under Blog, Business Solutions, Cost-effective, Database Software Development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.