Yii: When to use single table inheritance. Clarifying usage and understanding

Question

Assuming that I have an online store whereby products are stocked and sold by either integer quantity or decimal weight. There are different types of products. Not all items are related. I need to decide whether I should put them in separate tables (normalise) or use a technique called single table inheritance which enables me to store all products in the same table, but use Different model classes for each type of product.

A simple example being.

Rice would be is stocked per kilo (decimal) as opposed to per grain (integer). Rice would be sold by the kilo (decimal) but you cannot sell 1.5 apples (decimal).

Is this what single table inheritance is for or have I missed the point of what it is for?

DB Example

CREATE TABLE `product` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`unit` varchar(100) NOT NULL,
`stock` decimal(10,3) NOT NULL,
PRIMARY KEY (`id`)
);

INSERT INTO `product` (`name`, `unit`, `stock`)
VALUES 
('Rice', 'Kilo', 10.00),
('Apple', 'Each', 500),
('Orange', 'Each', 230),
('Flour', 'Kilo', 55.3),
('Coke', 'Litre', 123.5);

Models (Only Product and Kilo unit type shown for simplicity)

class Product extends CActiveRecord {
    ...
        STUFF
    ...
    protected function instantiate($attributes)
    {
        switch($attributes['unit'])
        {
            case 'Kilo':
                $class='KiloUnit';
                break;
            case 'Each':
                $class='EachUnit';
                break;
            case 'Litre':
                $class='LitreUnit';
                break;
            default:
                $class=get_class($this);
        }
        $model=new $class(null);
        return $model;
    }
}

class KiloUnit extends Product {
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }
    public function defaultScope()
    {
        return array(
            'condition'=>"type='Kilo'",
        );
    }

    public function rules(){
        array('stock', 'numerical'),
    }

    public function attributeLabels()
    {
        return array('stock' => 'Kilo');
    }

and rules for model 'EachUnit' would be something like

array('stock','numerical', 'integerOnly'=>true)

and attributeLabel for model 'EachUnit' would be something like

return array('stock' => 'Quantity');

That way, If I wish to work with all products, I can use the following:

$products = Product::model()->findAll();
foreach($products as $p)
{
   do something
}

If I only wish to deal with products with kilo as unit type

$products = KiloUnit::model()->findAll();
foreach($products as $p)
{
   do something
}

Answer 1

I have done a little more research on Single Table Inheritance as I was a little unfamiliar with the term, and have decided that this really deserves an answer rather than a bunch of comments.

Single table inheritance comes into play when you have a group of similar objects that need to be persisted into a database. You can either split the objects up into separate tables, or if they are similar enough you can keep them all in a single table with a field to identify which is which. the objects are different because they need different processing or have slightly different attributes. In your case I wouldn't consider different units of measure significant enough to call it Single Table Inheritance, but more generically you could have items stocked by volume and items stocked by quantity that both have the same set of attributes but different methods to deal with depletion of stock or costing, so I guess you could technically call it Single table Inheritance. But I don't even think that is significant enough since the methods for handling the two are even similar.

Here is an example of item classes that you might be more in line with this concept though. Think of a manufacturing application. In this case you have items, some purchased, some manufactured. these items share many of the same attributes, but a manufactured item would have a bill of materials, and attributes that have to do with the manufacturing process while the purchased items would have vendors, and attributes associated with the buying process. it is quite possible that all these attributes would fit in the same table, some would be null for purchased items, and some would be null for manufactured items, but using Single Table Inheritance you could search all the items with a single query, or deal with them with a class that know of their unique qualities. In this case you are saving some time on joins and simplifying the front end because the differences are much more striking, and SQL unions can be costly. But just for different units of measure? probably not as worth while.

Here is a link to the Yii documentation on Single Table Inheritance.

Answer 2

One thing I would probably change is that you could do is create a table called measurement then store an measurement_id in your product table. Then you can move some of your other functionality to the Measurement model instead of having it on the Product model. Then when you are creating a product you could easily create drop downs of all the measurement options. I don't know that there is necessarily a right or wrong way in this situation though.