Single table inheritance

Relational databases do not support inheritance so if we need to represent it, we have to somehow store meta info while keeping performance by minimizing JOINs. One way to solve this problem is using single table inheritance. All fields for the whole class tree are stored in a single table. Class name is stored in the type field of the same table.

Schema will be the following:

CREATE TABLE `car` (
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `type` varchar(100) NOT NULL,
    PRIMARY KEY (`id`)
);

Base AR model with overridden instantiate method:

class Car extends CActiveRecord {
    static function model($className=__CLASS__) {
        return parent::model($className);
    }
 
    function tableName() {
        return 'car';
    }
 
    /**
     * We're overriding this method to fill findAll() and similar method result
     * with proper models.
     *
     * @param array $attributes
     * @return Car
     */
    protected function instantiate($attributes){
        switch($attributes['type']){
            case 'sport':
                $class='SportCar';
            break;
            case 'family':
                $class='FamilyCar';
            break;
            default:
                $class=get_class($this);
        }
        $model=new $class(null);
        return $model;
    }
}

And, finally, our children:

class SportCar extends Car {
    static function model($className=__CLASS__) {
        return parent::model($className);
    }
 
    function defaultScope(){
        return array(
            'condition'=>"type='sport'",
        );
    }
}
 
class FamilyCar extends Car {
    static function model($className=__CLASS__) {
        return parent::model($className);
    }
 
    function defaultScope(){
        return array(
            'condition'=>"type='family'",
        );
    }
}

In the above we've overridden defaultScope so, for example, when you'll search using FamilyCar model, it will return only family cars.

Total 3 comments

#4270

mikl at 2011/06/21 11:38am

Attributes from related table

Thanks Alex for this very nice tutorial, really helpful.

I now have one situation, where the type is stored in a 1:1 related table. I first tried to always JOIN that record through with+together in defaultScope - but i can't access this value in instantiate().

So i suppose i always need an additional query in instantiate(). Something i tried to avoid. Any workaround for this?

#4165

yiqing95 at 2011/06/11 10:24pm

what's about save and update

use this tech , when i insert a new record which is subclass of car ,let's say SportCar , if i should manually give the type field a value 'sport', another word ,except query operation the insert , update scenario do we need care about the 'type' attribute ?

#4108

bglee at 2011/06/06 11:41pm

Table Inheritance: One Exception To The Rule

First, very nice article! For what it's worth, I just wanted to point out that Postgresql actually supports table inheritance and nicely. I came across the feature years ago, although I never used it in practice. I've spent most of my time in Oracle databases. I have no idea how it is implemented or if there is a performance hit. Oracle (since about version 9.x) has included so-called object-relational features which also allow you to do inheritance. I don't know if the method improved since then, but it was a very awkward implementation.

Please login to leave your comment.

Single table inheritance

Total 3 comments

Leave a comment

Yii Supporters