Design of Our Sample Database

Now that we have covered some OR concepts, it is time to set up our database so that we can move one step closer to building an application's user interface with the help of the Spring Web MVC Framework.

As I mentioned in earlier chapters, the focus of this book is more on development and less on infrastructure. Given Java's vendor product portability (for example, operating system, web/application server, databases), in theory, it should be relatively easy to develop your application using one product but deploy to another application. In light of this, I chose the easiest (and consequently lightest-weight) products to set up. HSQLDB, a relational database, is one such product (discussed later in this chapter), and we will use it for Time Expression.

Denormalization

Before we look at HSQLDB, let's revisit our domain model from Chapter 3, "XP and AMDD-Based Architecture and Design Modeling," shown in Figure 5.1.

Given the simplicity of our sample application, Time Expression, and its domain model, we could create a physical database model (PDM), also known as an Entity-Relationship (ER) diagram, which contains entities identical to ones in our domain model, with the addition of columns and data types and other database constraints. However, let's denormalize it just a bit for performance and ease of development purposes.

Figure 5.2 shows a PDM, denormalized a bit from our Domain Model and with data types (for example, varchar) added to it. The denormalization is related only to the Timesheet and Time tables.

Naming Convention

You will notice we are using Java-like naming conventions for the table and column names. This makes our job easier because we can use the same names across all artifacts related to Time Expression while also gaining consistency across them. In other words, we have matching names from User Story tag/name to controller classes to model (domain) objects to the Hibernate persistent bean Java code and finally, to the database tables and columns (shown in Figure 5.2).

This naming approach makes our job easier in two ways. First, we don't need to think about the naming convention for each layer, and second, it reduces the amount of mapping details we need to specify in our Hibernate class mapping files because we do not have to specify a corresponding column name for each property being mapped (as we will see later in this chapter).

However, in the real world, you might not have control over the database table/column naming because a database group might have their own set of naming standards. In this case, it is easy to use Hibernate's column attribute to specify the database column name. I would also encourage following your organization's naming standards for consistency sake.

Note that for database objects (such as tables and sequences), I tend to use names starting with an uppercase letter, whereas column names start with a lowercase letter.

Database Design Disclaimers

The following are some disclaimers and/or explanations for the PDM we looked at in Figure 5.2.

Unused Columns

By combining the Timesheet and Time entities into one physical table, there is the possibility of wasted database space by unused columns. For example, there is a good chance that MinutesSat and MinutesSun will be less frequently used (unless employees in this company work most or all weekends). However, the advantages of the simpler design and performance arguably outweigh the disadvantages of a bit of wasted space.

Int Versus Float

We have used Minutes<Day> columns to store fractional hours worked (for example, 30 minutes or 0.5 hour) in the Timesheet table versus Hours<Day> columns or even float data types. The reason we did this is because I want to demonstrate how we can use the Spring Web MVC framework (in Chapter 7, "The Spring Web MVC Framework") to do automatic data conversions between the UI and the database. Also, an int will typically take up less physical storage space than a float will (for example, 2 bytes versus 4 bytes).

Password

We have a Password column in the Employee table. Typically, in larger organizations, you might end up using something like a central Lightweight Directory Access Protocol (LDAP) authentication service. However, this works well for our small (and sample) application, Time Expression.

DDL Script

Now that we have a PDM (see Figure 5.2), we can move to the next level down, which is to write a DDL script that can be used to create the actual databse. Our DDL script is embedded inside one of our Ant scripts, named timexhsqldb.xml. The table names, column names, and data types in our DDL script closely match the PDM in Figure 5.2, as they should.

Our DDL file primarily contains CREATE TABLE statements. However, I would like to point out a couple of additional notable items.

First, the primary key column of the Timesheet table is of data type identity, as shown in this code excerpt:

CREATE TABLE Timesheet (    timesheetId IDENTITY NOT NULL,

As you might already know, an identity is an auto increment database column (and is directly supported by Hibernate). For databases that do not support identity types, we can use a sequence type instead.

Second, we have seen some test data being inserted; this is for use by our JUnit test cases covered later in this chapter. For the sake of simplicity, I have not created any primary or foreign key constraints, as we typically should in a real-world application. Also, the focus of this chapter is to demonstrate features of Hibernate and not necessarily database design.