Database engines include built-in mechanisms for transaction management. The application can begin a transaction and have it committed or rolled back. The database engine will ensure that uncommitted data will not be visible to other users.
Web applications cannot take advantage of transaction managers built into database engines. To ensure scalability, web applications maintain a pool of database connections shared by multiple users simultaneously. Transaction managers require that all modifications of data are performed in the context of a single open database connection. A typical web application may handle requests to modify data from a single user by utilizing multiple pooled connections.
Client-Side Caching
Developers can implement client-side caching of requests to update data. The application user modifies several data items on the client and requests to submit changes at once. The entire batch of modifications is sent to the web application server for processing. The server code will parse the batch and perform individual updates using a database transaction.
Users will have to stay on the same web page until the data is submitted. There is a potential for data loss if a browser window is accidentally closed. The data entry sessions must be short.
The application developer has to rely heavily on JavaScript when manipulating data. Every data entry screen becomes a specialty project.
Server-side Data Staging
A substantially more flexible solution is to “stage” data on the server. Data records are submitted straight to the database tables, and marked as “Draft” using various techniques. When the draft data is ready to be included in the main dataset, the status of the data rows is changed to “Committed”.
Users can submit draft data from multiple pages. The data is safely stored in the database instead of volatile browser cache. The drafted data may be perfected over any period of time from multiple devices.
Application developers can take full advantage of SQL when manipulating draft data. Code On Time web applications include mechanisms that make data staging implementation trivial.
Data Staging Strategies
Suppose that you have created an Order Form web application from the Northwind sample database. Each order is stored in two tables: dbo.Orders and dbo.[Order Details]. When a user wants to create a new order, they must first create the order record, and then add a few order details.
The user should have the opportunity to add or change details, or drop the order altogether before committing it to the primary data set.
However, these “draft” orders will be included in all data sets, such as Order Subtotals. It is necessary to segregate the draft order data from the primary data set.
Consider the following methods of draft data staging when implementing web transactions.
- “Status” Field
This method involves adding a “Status” field to the Orders table that will indicate whether the order is a draft or has been committed. The status of new orders will have the default value of “Draft”. A custom action will be added to the order form to submit the order and change the status to “Commited”.
- Log Table
This method creates a table that holds a log of all draft orders. When a new order is created, a reference will be created in the draft order log table. When the data is submitted, the reference will be deleted from the log table.
- Data Staging Tables
This method requires creation of duplicate “staging” tables that will hold the draft data. When the order is ready to be submitted, it is copied to the original tables and deleted from the staging tables.
The first two methods will use dynamic access control rules in order to prevent committed data from being displayed on the order form, and draft data from being displayed on all other pages.