Object/Relational Modeling (MySQL & mSQL)

8.3. Object/Relational Modeling

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 several reasons.

Objects do not store only simple data in their attributes. They may store collections or relationships with other objects.
Most relational databases -- MySQL and mSQL among them -- have no way of modeling inheritance.

Rules of Thumb for Object/Relational Modeling

Each persistent class has a corresponding database table.

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

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

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.

Think about that address book we talked about earlier. We probably have something like the address and person tables shown in Figure 8-1.

NOTE

The least apparent issue facing programmers is one of mindset. The basic task of object-oriented access to relational data is to grab that data and immediately instantiate objects. An application should only manipulate data through the objects. Most traditional programming methods, including most C, PowerBuilder, and VisualBasic development, require the developer to pull the data from the database and then process that data. The key distinction is that in object-oriented database programming, you are dealing with objects, not data.

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

Figure 8-2 shows the object model that maps to the data model from Figure 8-1. each row from the database turns into a program object. Your application therefore takes a result set and, for each row returned, 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-2. The object model supporting a simple address book application

With just a tiny amount of extra complexity to the object model, we can add a world of complexity to the challenge of mapping our objects to a data model. The extra bit of complexity could be to have Person inherit from Entity with a Company class also inheriting from Entity. How do we capture an Entity separate from a Person or a Company? The rule we outlined above is actually more of a guideline. In some instances, the base class may be purely abstract and subsequently have no data associated with it in the database. In that instance, you would not have an entity in the database for that class.