Many system architects have postulated that software development has little to do with solving new problems. After all, if you look at the functions performed by the software systems in use now vs. those in use 20 years ago, the problems themselves are fundamentally the same. Accounting, sales order processing, purchase order processing, fleet management, and almost any other software system have the same fundamental business rules. But new software tools, hardware platforms, and connectivity options allow architects and developers to approach some of the same business problems in radically different ways.
And many of these business problems use the same core software architecture. For example, there are lots of business systems that need to handle transactions that contain similar, but not identical detail records. Let’s consider the example of an expense management system.
Identifying the problem
The goal of an expense management system is to enable its users to submit records of all of their business activity for the purposes of reimbursement and categorization. The individual wants to be repaid for expenses that he or she paid on behalf of the company. The company wants to make sure that the expenses incurred are properly booked against the company’s internal accounts. If you look at a paper copy of a business expense form, it appears to be a simple process to translate its fields into a database schema. But as you drill down, the problem becomes more complex.
The complexity lies in the representation of individual expense line items. It’s easy to create an expense report header that contains company and individual identification as well as report specific details, such as the time period in which the expenses were incurred and whether any advances were made against the expense report. All of the line items may have the date and a reason for the expense, but that’s where the similarity ends. Mileage entries may contain fields such as where the individual traveled to and from and start and end odometer readings. Hotel bills contain details such as taxes, business calls, and tips. The IRS requires that entertainment entries identify the individuals entertained and their business relationship to the individual.
The relational database solution
Relational database proponents will yawn at this problem and design a solution involving a set of related tables. An expense report Header table will contain items relating to the report as a whole. An expense report Detail Summary table will contain items common to all of the expense report lines, such as the date and description. They will also have a pointer embedded in each Detail Summary record that points to another table that contains details for that record type.
For instance, a Mileage Detail table will have records that contain a unique key stored in the Detail Summary table along with the details (e.g., mileage rate, from, to, and odometer readings) for that particular record. An Entertainment Detail table will contain the IRS-required entries and will have its unique key stored in the Detail Summary table. When listing the expenses for a particular report, the developer simply iterates through the Detail Summary table and links to the proper underlying expense Detail table to retrieve the details (when necessary).
Although this problem can be solved using relational database technology, the solution ends up being difficult to code against and requires developer intervention to extend. It’s difficult to code against because of the requirement to manage links to external tables to print a simple expense report. It requires developer intervention to extend because every new detail type requires its own external table and requires the developer to rewrite the expense report printing and data entry mechanism to accommodate the new detailed type.
The XML/XSD/XSLT solution
But there is a much better way to solve this problem. By combining the Detail Summary table and the individual expense Detail tables into a single table using XML and XSD, you can create a more elegant and more easily extensible solution. To do this, you would first define an Expense Report Types table. This table would contain rows that define the individual expense types (i.e., one row in the table defines a Mileage entry where another row defines the Hotel entry).
One of the columns in each row would contain an XML schema that defines the fields that should be saved for this type of expense entry. The Mileage row, for example, would have an XSD with the fields MileageRate,From, To, OdoStart, and OdoEnd, where the Hotel row might contain repeating groups of fields for room rate, city tax, state tax, business phone calls, and personal phone calls. The schema should also contain an expression called Amount with the formula used to calculate the amount charged for that entry (e.g., Amount would be OdoEnd minus OdoStart times MileageRate to calculate the total amount for reimbursable mileage).
Each expense row would also contain two columns with XSLT expressions for entry and printing. The XSLT entry column formats the XSD column for onscreen display and data entry. The XSLT printing column formats the XSD column for printing a copy of the expense report.
Using this structure, the Detail Summary table would include not only the date and reason fields (as before), but also a descriptor that points to its record type in the Expense Report Types table, a single block of XML that represents the detail, and an Amount column for quick summarization. The developer could use common code to read the XSLT entry column, display an entry form, collect the data based on the XSD schema, place the resulting XML document in the Detail Summary table, call the Amount expression, and place the value returned in the Detail Summary amount column. An expense report could be printed in a similar fashion, using the XSLT print column from the Expense Report Types table.
This solution is more flexible, and it's easily extensible without writing any additional code. For example, if a user wants to add additional fields to the Mileage entry type, he or she can edit the XSD and the XSLT entry and print fields in the Expense Report types table and the entire interface and reporting layout will change automatically. Adding a new expense type involves simply adding a new row to the Expense Report Types table. The expense entry application could dynamically add this type, display an entry form, and print out the result.
Expense reporting is just one of the application types whose flexibility can be greatly enhanced by the creative use of XML standards. The ability to accommodate dynamic data storage using XSD and XML and dynamic data entry and display using XSLT is a powerful tool that no system architect should ignore.