9.2 A Simple Database

All of the examples in this chapter were tested with the MySQL database management system (DBMS). ^[3] "Setting Up the MySQL Database Used in This Book" on page 556 provides instructions on downloading and installing MySQL and an associated JDBC driver, in addition to showing you how to create and populate the database used in this chapter.

^[3] An exception is the example discussed in "Executing Database Transactions" on page 411, which uses the Cloudscape database because MySQL does not implement database constraints.

The examples in this chapter access a simple database with three tables: Customers , Orders , and Accounts . Figure 9-1 shows the data contained in the Customers table.

Figure 9-1. The Customers Table

Initially, the database contains 20 customers. Each customer has a customer ID, name , phone number, and address. In the interest of simplicity, all of the (fictitious) customers reside in North America.

Figure 9-2 shows the Orders table and the Accounts table.

Figure 9-2. The Orders Table (left) and the Accounts Table (right)

Each order in the database contains an order number, an order date, a customer ID, an amount, and a description. For the sake of brevity, only the first seven customers in the database have orders. All of the customers have accounts. The Accounts table is only used by the example in "Executing Database Transactions" on page 411.

For the sake of illustration, the tables shown in Figure 9-1 and Figure 9-2 use a simplified design; for example, the Orders table keeps track of orders for single items with no notion of quantity and directly stores order amounts and product names that are localized for English. A better design would include an additional Products table that listed product IDs and prices and a modified Orders table that stored product IDs instead of descriptions and quantities instead of amounts. Moreover, you should never store localized data in a database, because you will want to easily localize that data when you display it. In general, you should adhere to the following rules when storing data in a database:

• Store dates and times according to ISO 8601, which defines locale-independent formats for dates and times. ^[4] The Orders table adheres to this rule by storing dates in the format YYYY-MM-DD, as defined by ISO 8601.

  ^[4] See http://www.cl.cam.ac.uk/~mgk25/iso-time.html for more information about ISO 8601.

• Store products with numeric IDs instead of localized descriptions. You can map those IDs to descriptions with separate tables in the database or with resource bundles in your application. See "Resource Bundles" on page 259 for more information about resource bundles and how you can use them to localize text.

Store prices in a locale-independent manner in a fixed representation, such as the format defined by the java.lang.Long.toString or java.lang.Double. toString methods . The Orders and Accounts tables in Figure 9-2 store their currencies in that manner.

Note that the examples in this chapter are not internationalized. The database design and the lack of internationalization in this chapter are intentional because proper database design and internationalization would obfuscate the concepts that this chapter illustrates, namely, how to use the JSTL SQL actions.