Lesson Wednesday

Now that we have learned that we can create many different "flavours" of objects that all inherit from an abstract class or parent class, you may assume that we're obligated to create two separate database tables to house their information, or create complicated join tables in order to be able to retrieve information. However, this isn't actually the case!

Because parent and child objects are so similar, we can actually store their data on the same table, even though they technically belong to separate classes! This database architecture is known as single table inheritance.

Single Table Inheritance

Single table inheritance is the practice of storing objects of similar types (usually those that inherit from each other or the same abstract class) in the same table despite differences in properties. This may sound like a mess, but it's actually surprisingly simple! Here's how it works:

  • The single table includes columns for all attributes of all interrelated types in a single table. Even if not every object has every attribute.
  • The table also contains a type column. This column contains information regarding what type each entry is.
  • If an object doesn't have data that corresponds with one of the columns, that column in its entry is simply left blank.

Let's work through a simple example to explore these concepts. We'll consider building an app for a Furniture store. There are many kinds of Furniture, but we'll stick with just a few: Table, and Bookcase.

Some Basic Data Models

Assume that all Furniture has some qualities that all classes share: woodType and weight, for example. Table also has the properties surfaceArea and legCount. Bookcase will have the properties shelfCount and height. We'll make Furniture an abstract class, meaning it only holds inheritable methods and properties, but doesn't ever get instantiated.

Furniture.java
public abstract class Furniture {
    private double weight;
    private String woodType;
    private boolean inStock;
}
Table.java
public class Table extends Furniture {
    private int legCount;
    private double surfaceArea;
    private int tableId;

    public Table(double weight, String woodType, boolean inStock, int legCount, double surfaceArea) {
        this.legCount = legCount;
        this.surfaceArea = surfaceArea;
    }
}
Bookcase.java
public class Bookcase extends Furniture{
    private int shelfCount;
    private double height;
    private int bookcaseId;

    public Bookcase(double weight, String woodType, int shelfCount, double height) {
        this.shelfCount = shelfCount;
        this.height = height;
    }
}

When to Use Single Table Inheritance

As we'd expect, all of our classes have a lot in common, which is why single table inheritance works well here: single table inheritance is only meant for similar objects that share large amounts of attributes.

You do not want to include distinctively different types of objects that don't share many attributes on the table. Especially if those objects are supposed to have a relationship with each other. We wouldn't use single table inheritance to store Departmentss and Furnitures or Tasks and Categorys on the same table.

How Single Table Inheritance Works

The attribute under the type column denotes which class the database entry belongs to. This is known as a discriminator column. We can use it to filter which type of data belongs where.

Also, know single table inheritance isn't required for similar classes, it's simply one approach. It would not be incorrect to store each type of Furniture in their own table, either. They could still each have a one-to-many relationship with Department, and we could still list all of them.

Sketching our Schema

Here is what our big "single" table would look like:

furniture
-------

 id | type     | weight | woodtype | legcount | surfacearea | shelfcount | height
 ---+----------+--------+----------+----------+-------------+------------+--------
 1  | table    | 45.0   | ash      | 2        | 30.5        |            |         
 2  | bookcase | 18.5   | ash      |          |             | 5          | 78.5   
 3  | table    | 30.75  | pine     | 4        | 22.75       |            |
 4  | bookcase | 32.0   | oak      |          |             | 3          | 48.0

Deciding Between Single Table Inheritance and Multiple Tables

So, how do you decide when to use single table inheritance, and when to store classes in their own, separate tables?

  • If your application is treating two classes that inherit from a singular superclass as essentially the same object, just with a few specialized attributes/methods for each, you should probably use single inheritance.

  • If your application treats two classes as different objects (even if they inherit functionality from the same class, for the sake of keeping code DRY), you should probably use separate tables.

  • Additionally, if two classes have more unique attributes than shared attributes, you probably shouldn't use single table inheritance.

Discriminator Column

Now that we know a little bit about how this works, let's walk through implementing it together. First, let's update our database to contain a discriminator column. As we just discussed, this column will contain information regarding what class each entry belongs to. It's common practice to name the discriminator column type.

Class Attributes

Next, we'll add a corresponding type property to our Furniture class, so each of our two types of furniture will automatically inherit it:

public abstract class Furniture {
  ...
  public String type;
...

Because type will always be the same for all Bookcases and the same for all Tables, we'll declare a String constant in both classes containing their type, and use this to set each furniture's type attribute in the constructor:

public class Table extends Furniture {
  private int legCount;
  private double surfaceArea;
  private int tableId;
  private static final String DATABASE_TYPE = "table";

  public Table(double weight, String woodType, int legCount, double surfaceArea) {
      this.legCount = legCount;
      this.surfaceArea = surfaceArea;
      type = DATABASE_TYPE;
  }
}

We would do the same with Bookcase, and then make sure we included this property in any save methods we wrote for our DAO implementation. If we are starting from scratch, we need to account for this property, if we are adding this to an existing project, we should edit our methods, test, and then delete and rebuild our database table as well.

Retrieving data from a single table

You are likely wondering how we deal with empty fields when returning data, but it's actually much easier than you might expect,(given that you have likely wrestled with Sql2o mapping exceptions already and they can be a bit tricky...)

Have you ever seen a GitHub issue before? It's when someone reports a problem, or missing feature in an open source tool, like sql2o.

Take a look at this GitHub issue in sql2o's repo. As one of its creators states, single table inheritance is so common, that the most frequently-requested new feature in the sql2o was the option to ignore empty columns when retrieving information in a single table inheritance setup. And, they've just recently added the feature!

By adding a call to .throwOnMappingFailure(false) to our Sql2o method chain, we can simply ignore empty fields. Let's look at an example:


@Override
public List<Bookcase> getAllBookCases() {
    try(Connection con = sql2o.open()){
        return con.createQuery("SELECT * FROM furniture WHERE type = 'bookcase'")
                .throwOnMappingFailure(false)
                .executeAndFetch(Bookcase.class);
    }
}

This method should accurately return a list of Bookcase objects. You can easily adapt this method to return Tables, and ArrayList<Object> can of course hold both, and you could easily retrieve the size() of that ArrayList in your app to show the total Furniture count.