Single Table Inheritance with PDO

Single Table Inheritance is a way to simulate object oriented inheritance with a relational database.

That's a bit abstract, so let's give an example.

Say you have a set of Domain Models hidden behind a common interface. All of these domain models have and use the same set of values to do their work, but do these things in a different way with those values.

Using single table inheritance is a way to use an some sort of object relational mapping (whether that's a full blown ORM or just a data mapper) to store a domain model's values in an a relational database.

Expanding the Example

A very simple example of this would be a way over-engineered calculator: every operation might take an operand as an argument to a method and use a stored database value as the other operand. Each operation class would use a different operator.

Note: the examples here are fairly brief, but you can see a full set of examples here.

Here's the common interface:

<?php
interface Math
{
    /**
     * Do the math operation with the input value as one operand and the
     * database-stored value as the other
     */
    public function operate($input);

    /**
     * getter for the database-stored operand
     */
    public function getValue();
}

The typical implementation would involve a single entity class with a switch statement that checks some column for the operation type and performs an appropriate operation.

<?php
class DefaultMath implements Math
{
    const TYPE_ADDITION     = 'add';
    const TYPE_SUBTRACTION  = 'sub';

    // values from the database
    public $math_type;
    public $math_value;

    public function operate($input)
    {
        switch ($this->math_type) {
            case self::TYPE_ADDITION:
                return $input + $this->getValue();
                break;
            case self::TYPE_SUBTRACTION:
                return $input - $this->getValue();
                break;

            // ... TYPE_MULTIPLICATION, etc
        }
    }

    public function getValue()
    {
        return $this->math_value;
    }
}

This isn't bad, but for anything beyond a simple operation the class has a potential to get very large and unwieldy. It also violates the single responsibily principle.

Instead we'd split our operation into several different classes, using an abstract base class to cover the common functionality.

<?php
/**
 * Base class for things
 */
abstract class AbstractMath implements Math
{
    public $math_type; // unused here
    public $math_value;

    public function __construct($value=null)
    {
        $this->math_value = $value;
    }

    /**
     * {@inheritdoc}
     */
    public function getValue()
    {
        return $this->math_value;
    }
}

class Addition extends AbstractMath
{
    /**
     * {@inheritdoc}
     */
    public function operate($input)
    {
        return $input + $this->getValue();
    }
}

// class for Subtraction, Multipliation, Division, etc

Now we have a framework and set of objects to work with, let's talk about how to use PDO to make single table inheritance work.

PDO & Single Table Inheritance

PDO comes with a set of constants that define how things get fetched. The one we're interested in is PDO::FETCH_CLASSTYPE. If PDOStatement::fetch, PDOStatement::fetchAll, or PDO::query is called with FETCH_CLASSTYPE the first column of each row is treated as a class name.

Here's our example schema:

CREATE TABLE sta_math (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    math_type VARCHAR(255) NOT NULL,
    math_value INT NOT NULL,
    PRIMARY KEY (id)
);

math_type is going to be the class name and math_value is our stored operand.

Inserting values (fairly normal stuff here):

<?php
$dbh = new \PDO(/* ... */);

$add = new Addition(10);
$stm = $dbh->prepare(
    "INSERT INTO sta_math (math_type, math_value)"
    . " VALUES (:math_type, :math_value)"
);
$stm->bindValue(':math_type', get_class($add));
$stm->bindValue(':math_value', $add->getValue(), \PDO::PARAM_INT);
if (!$stm->execute()) {
    throw new \UnexpectedValueException('Unable to execute insert statement');
}

And here's how we'd fetch values back into the entity class stored in the math_type column.

<?php
$dbh = new \PDO(/* ... */);

$stm = $dbh->query(
    "SELECT math_type, math_value FROM sta_math",
    // FETCH_CLASS is required, FETCH_PROPS_LATE is to deal with our `null`
    // default in AbstractMath's constructor
    \PDO::FETCH_CLASSTYPE | \PDO::FETCH_CLASS | \PDO::FETCH_PROPS_LATE
);

$add = $stm->fetch();
$add instanceof Math; // true
$add->operate(10); // 20

The downside here is that how the queries are written determines the output. A developer changing the order of columns in the query would break things. Similarly, change your class names without updating the database would also break things.

That said, for separating complex logic and replacing conditionals with polymorphism this approach could work well.

#