Object/Relational Modeling (Managing and Using MySQL, 2nd Edition)

8.3. Object/Relational Modeling

Accessing a relational database from an object-oriented environment exposes a special paradox: the relational world manipulates data directly while the object world encapsulates data behind a set of behaviors. In an object-oriented application, the database serves as a tool for saving objects across application instances. Instead of seeing the query data as a rowset, an object-oriented application sees query data as a collection of objects.

The most basic question facing the object-oriented developer using a relational database is how to map relational data into objects. Your immediate thought might be to simply map object attributes to fields in a table. Unfortunately, this approach does not create the perfect mapping for two reasons:

Objects do not store only simple data in their attributes. They may store collections or relationships with other objects.

Most relational databases—including MySQL—have no way of modeling inheritance.

An application should manipulate data only through the objects. Most traditional programming methods, including most development in C, PowerBuilder, and VisualBasic, require the developer to pull the data from the database and then process that data. Thus, the first task of an object-oriented application is to grab the data from the database and instantiate objects, through which all further application processing takes place.

Think about an address book application. You would probably have something like the address and person tables shown in Figure 8-5.

Figure 8-5. The data model for a simple address book application

Figure 8-6 shows the object model that maps to the data model from Figure 8-5. Each row from the database turns into a program object. Your application therefore takes each row from a result set and instantiates a new Address or Person instance. The hardest thing to deal with here is the issue mentioned earlier: how do you capture the relationship between a person and her address in the database application? The Person object, of course, carries a reference to that person's Address object. But you cannot save the Address object within the person table of a relational database. As the data model suggests, you store object relationships through foreign keys. In this case, we carry the address_id in the person table.

Figure 8-6. The object model supporting a simple address book application

Often, you need to add an extra layer to your object model to permit the flexibility provided by object-oriented programming and inheritance. For instance, you could create a base class called Entity and let both Person and Company inherit from it. Thus, the rules of thumb for object/relational modeling include:

Each persistent class has a corresponding database table.

Each row from a database table corresponds to an instance of its associated persistent class.

Object fields with primitive data types (integers, characters, strings, etc.) map to columns in the associated database table.

Each many-to-many object relationship requires a join table just as database entities with many-to-many relationships require join tables.
Inheritance is modeled through a one-to-one relationship between the two tables corresponding to the class and subclass.

In some instances, the base class is purely abstract and subsequently has no data associated with it in the database. In that call, no entity would appear in the database for that class.