5.2 Relational Modeling Constructs

Before we discuss the mapping between Java classes and a relational schema, we will first provide a brief summary of the modeling constructs found in relational schemas. This is not meant to cover all aspects of a relational schema; it will simply define the terms we use in this chapter.

A relational schema is organized as a set of tables. A table is usually defined for each entity in the application domain you are modeling. When you design an object model, an entity is represented by a class. Each table consists of rows and columns. A row contains the data for a specific instance of an entity being modeled. A column contains the values for one of the attributes of the entity. A table cell is the intersection of a particular row and column in the table, and it contains the value of an attribute for a specific entity instance.

The type of a column is the same for all rows of a table. Relational databases do not support Java's capability for a field to reference one of many different types. ANSI SQL 92 defines a standard set of supported column datatypes. Relational database products support these standard datatypes and usually support their own additional, proprietary datatypes. One issue developers often contend with is the use of a datatype that is specific to one database product but not supported by another. JDO helps insulate your applications from these datatype differences, since you only deal with Java types, which are then mapped to the various underlying datastore types.

Often, one or more columns are defined as the table's primary key to identify a row uniquely. A table can have only one primary-key constraint. The primary-key constraint requires that the columns have a unique value for each row, and the primary-key columns cannot contain a null value.

One or more columns in a table may be defined as a foreign-key constraint, which is used to enforce referential integrity in the datastore. A row's foreign-key columns contain the same values as columns in a specific row of the referenced table.

A relationship between the rows of tables can be coerced by specifying a join condition, which is an expression that uses the columns of the tables being joined. Primary-key and foreign-key constraints can be used to define relationships between tables, and, they can be used as the basis of a join. To establish a relationship between table A and B, where table B has a foreign key referencing table A, a join condition requires that the foreign key in B is equal to the primary key in A. This is the primary means of expressing a relationship between rows, so relational databases have optimized their performance of these join conditions using indexes. But it is not necessary to use columns in primary- and foreign-key constraints to perform a join; any columns in the tables may be used to establish an association among tables.

A table may have one or more indexes, associated with one or more columns. Indexes are used to optimize the performance of access to rows with specific values or a range of values for one or more columns. Indexes help optimize the performance of join operations.

5.2.1 SQL 99

The SQL 99 specification includes some support for defining object constructs in SQL. It has introduced the notion of table inheritance: a table can have subtables. In addition, a column can contain structured datatypes, such as arrays and User-Defined Types (UDTs). You can also define inheritance hierarchies of UDTs.

At this time, the level of support for SQL 99 varies considerably among relational databases. Some databases do not support any of the constructs defined in SQL 99. Others have implemented only a subset of its facilities, sometimes with nonstandard syntax.

Many applications do not use the object capabilities found in those databases that do support them. Many developers defining objects in languages like Java prefer to specify their object model once in Java and then use an interface like JDO to map their Java modeling constructs to the underlying datastore. As the relational database vendors broaden their support for SQL 99 object constructs, JDO implementations will be able to map the Java models onto the SQL 99 constructs, based on customer demand. The examples in this book do not assume the availability of SQL 99 facilities.