Classes to tables

--Originally published at Hackerman's house

When you have to map objects to relational databases, the place where you have to start is with the data attributes of the class. It is important to define in how many columns these attributes will be mapped, it can be from zero columns to many more. Not all attributes are persistent, this means that they are used for some temporal calculation in the application, this has a direct impact in the way the database is designed because it’s possible that this attribute is not necessary in your database. In the case that an attribute is an object in their own, the attribute will map into several columns in the database.

The easiest mapping is when each attribute goes into a single column. In this case the class model and the physical data model can be very similar; the main differences will be that the data model needs at least one primary key, and in case there are more classes interrelated you need to add the foreign keys for the occasion.

Inheritance

Inheritance is a big deal when mapping a class into a table. The issue is figuring out how to organize the inherited attributes in your model. There are three fundamental solutions for this.

This class hierarchy will be modified using the three approaches.

Figure 1. A UML class diagram of a simple class hierarchy

Using one data entity for an entire class hierarchy.

This means that you will map an entire class hierarchy into just one data entity that contains all the attributes of all the class in the hierarchy. It is important to add the primary key to the data entity. The advantage of this approach is that you have all the information available in just one table; this supports polymorphism in an easy way. The cons are that has a high level of coupling and an error

Figure 2. Mapping the class hierarchy to one single data entity.
Figure 3. Mapping each concrete class to a single data entity.
Figure 4. Mapping each class to its own data entity.
the attributes could have meaningful affectations in the system.

Figure 2. Mapping the class hierarchy to one single data entity.

Using one data entity per concrete class.

In this approach each data entity includes the attributes of the specific class and the inherited attributes. The advantage is that it is easy to get the data because it is stored in only one table. One of the main disadvantages is that when you modify a class you need to modify all its subclasses.

Figure 3. Mapping each concrete class to a single data entity.

Using one data entity per class.

In this approach you create one table per class. Each subclass has a foreign key to contact it with the superclass. The main advantage is that it suits well in an object-oriented environment. When you modify a class, you don’t need to modify the subclasses. The main disadvantage is that there will be many tables in the database, one for every class.

Figure 4. Mapping each class to its own data entity.

Reference:

https://www.ibm.com/developerworks/library/ws-mapping-to-rdb/