Scott Stanfield
Vertigo Software, Inc.
September 1998
Summary: Discusses the limitations encountered in the development of FMCorp, the Web-based expense report system created by Vertigo Software, Inc. (4 printed pages) Also discusses improvements for the next version.
What would we do for FMCorp version 2.0? The following list illustrates limitations we encountered along the way. We have documented them here in order to address them for the next version of FMCorp.
While the Web site has impressive security constraints, once a user is logged in, they are lumped into one of two classes of users: employees or managers. Managers essentially have too much power because they can use the two administrative pages to change the User and Limits table.
It would be better to add a new column to the User table that specifically tags an employee as having administrative capabilities.
Instead of restricting just at the line-item level, add another limitation that checks the total expense report. One easy way to do this is to add some logic to the ExpenseItem table trigger. Every time a new item is added to the table, check the current total for that entry. Care must be taken to handle the case where multiple items are being inserted for a new expense report.
Note This feature was added during testing because we felt it better illustrated the concept of triggers rolling back an entire transaction. The side effects were minimal: an extra column was added to Users and more logic was added to tr_ExpenseItems_iu.
The fact that the User.ManagerID foreign key relates to User.UserID makes this table a bit confusing. There may be a better way to express this relationship. If future enhancements to the User table require special fields just for the managers, a different design might be warranted because it might violate database normalization rules.
We believe there is a performance penalty using Microsoft® Visual Basic® components and Microsoft Transaction Server (MTS). The first time the component is brought into memory takes about 10-15 seconds. As long as the MTS package hasn't timed out, subsequent logins are almost instantaneous.
We would rewrite the login component to use a Microsoft Visual C++® Active Template Library (ATL) component first to test whether this is a Visual Basic issue. If it appears to be, we would research the problem on MSDN or log a tech support call.
There's really no reason why expense reports need to be submitted to the database right away. It would be perfectly reasonable to use Microsoft Message Queue Server (MSMQ) to batch process a bunch of submissions. The employee could be notified via e-mail if there were any problems during submission.
An employee's manager has no idea when a new expense report has been submitted. They're responsible for periodically checking the Web site. It wouldn't be too difficult to send the manager a piece of e-mail when a new report is added to the database.
We could add this code to ExpenseReports.SubmitXMLFile. The method could use a few methods to look up the manager ID and e-mail address, and then use ExpenseMail to send off a message.
The e-mail message could include a summary or total amount for the user. A hyperlink could include enough information that would enable the manager to easily approve the report without a lot of navigation.
This is an obvious oversight. It would be easy to use RuleZero to generate another administration page.
The only piece missing in the SQL Server 7.0 support is the actual database device creation. This process was changed from SQL Server 6.5 to 7.0.
We could write a Java language application in the Microsoft Visual J++™ development system for Java that would allow an easy way to create and submit an expense report without leaving the Web environment. This solution would also eliminate the need to have Microsoft Excel set up. The user could obtain the latest Java Virtual Machine (VM) right from the Web site.
Web farms that use round-robin DNS do not work with Active Server Pages (ASP) that rely on Session variables. The Microsoft Visual InterDev™ Web development system has an ASP page property called "Sessionless ASP Page" that might help in this case. Another solution would be to use Active User Objects, which are part of the Site Server.
Active Server Pages include features that make it easy to detect the client browser. A set of pages could be offered that don't depend on Internet Explorer 4.0 features like roll-over menus (style sheets) and title animation.
If a new expense report template was created, an employee would have no way of knowing if they had an outdated version until they submitted their XML file. The components could attempt to support older versions. Another solution would be to send an e-mail notification to users when new templates become available.
For disconnected or traveling users, it might be more convenient to e-mail the XML file rather than connect to a Web site. The e-mail message would sit in their queue until they connect to the Internet or corporate network. A special e-mail account could be set up to listen for the new files, and then use the SubmitXMLMethod to add the XML file to the database.
Instead of populating the User table manually, the user list could be extracted dynamically from Active Directory Service Interfaces (ADSI).
There is no audit trail for the submission and approval process. It wouldn't be too difficult to add support directly in the stored procedures to record actions in a log file.
Our early component designs required more components to "know" about each other. We were able to decouple the components by passing more relevant information as extra parameters.
Consistent use of parameter order and naming made it easier to test and debug these components. Besides always passing the DSN as the first parameter, the last parameter for methods that leave side effects returned a status message.
We determined that methods that have reversible side effects are the only ones that should require transactions. We separated the database writes from the reads into the two components, ExpenseWriteDB and ExpenseReadDB. Only ExpenseWriteDB requires transactions.
Our early ASP implementation was modifying the database directly in the ASP code. Not only is this approach not easily scalable, but it was not easy to test and it made the ASP code bulky. Migrating this code to the ExpenseWriteDB components was straightforward.
The Web pages that depend on distributed transaction coordinators (DTCs) and stored procedures bypass the middle-tier Visual Basic components. The downside of this is that it more tightly couples the Web page to the database.
Affectionately known as a "post-mortem" review, recording your ideas about what you could do better next time is extremely important. It allows a sense of closure and records your ideas while they are still fresh.
Scott Stanfield is the president of Vertigo Software, Inc. Vertigo Software is a San Francisco Bay Area-based consulting firm that specializes in the design and development of Windows DNA applications and components. He can be reached at scott@vertigosoftware.com or on the Web at http://www.vertigosoftware.com/.