Database Design

Often, in sophisticated applications the database design is one of the most crucial steps to a successful implementation. Vshift's ColdFusion team has been dealing with database-intensive web applications for a while now, so the team was prepared to design the database with growth in mind. That being said, the database for this application does not need to be very complicated. The team decided to create only two tables for this database (Figure II-3.1). The two tables are the Users table and the Reports table. The Users table will hold the user's user name, password, and access level, plus a unique ID assigned to the user (Figure II-3.2). The Reports table will hold the report unique identifier, submit date, total, title, status, return reason, actual report data, and unique identifier of the user who has submitted the report (Figure II-3.3). The status field will be a text field describing the status of the order; possible values are pending, returned, approved, and deleted. The report data will be a large chunk of serialized text describing the report objects.

Figure II-3.1. This database diagram shows the tables and fields of the expense application database.

Figure II-3.2. The Users table design shows the field names, data types, and descriptions.

Figure II-3.3. The Reports table design shows the field names, data types, and descriptions.

One interesting aspect of this database design is the wddxdata field. Vshift could have designed this part of the database in several different ways, depending on the desired function of the data. Here are some of the ways the expense report details could have been stored:

• A report-details table could have been created to contain all of the report's possible data elements, and one entry would be stored in this table for every expense report item.

  The problem with this method is that first Flash would create an object to hold all of the report information, and then, before it was sent to ColdFusion, the object would need to be broken down into its respective elements and stored in the database. This process could involve a complicated logic loop to iterate through the data properly. Then when the data was needed in Flash again, the entire process would need to be reversed.

• An XML schema could be employed to communicate between Flash and ColdFusion to store all of the report details in one XML packet and just send the packet back and forth.

  While this solution may be more of an accepted industry practice than the previous method, it still suffers from basically the same problems. To create the XML packet, Flash would still have to perform a complex serious of loops to convert the native Flash object into a well-formed XML packet and back again.

• The method Vshift chose was to utilize the benefit of ColdFusion MX's support for native Flash MX objects sent via Flash Remoting. Flash can create the report object as an array object and send the entire object over to ColdFusion. ColdFusion can then serialize the object into a WDDX packet (this simply converts the object into an XML-compliant text description that can be easily stored in a large text object in the database). Now when Flash needs to use this object again, it is simply retrieved from the database via ColdFusion, deserialized back into the initial object, and sent back to Flash. Flash can then use the object as though it had never left. That process is described in detail later in this chapter.

By utilizing this method, we ensure that the only conversions necessary for database storage and retrieval will be the ColdFusion WDDX conversions. However, these conversions are automatic for ColdFusion MX and can be processed very quickly without much intervention by the developer.

NOTE ColdFusion MX can serialize and deserialize between complex structures and their text equivalents quite easily via the <CFWDDX> tag. Here are two examples.

To serialize an object into WDDX text:

 <cfwddxaction= "CFML2WDDX"input="#TheObjectVar#"output= "TheWDDXTextVar"> 

To deserialize WDDX text back into an object:

 <cfwddxaction= "WDDX2CFML"input="#TheWDDXTextVar#"output= "TheObjectVar">