Database Table inheritance with Doctrine

Database Table inheritance in Doctrine

Have you came across a situation when you need to apply Object Oriented Inheritance principle in database design? Did you have two tables sharing some fields and you wanted to connect them together without having to run queries or parallel insertion? Search no more as Doctrine supports this using Class Inheritance with discriminatorMap.

At the end of this post, you’ll learn how to create a product table (super class) and 3 different product category tables (furniture, food, online downloadable items) that inherent product table so when you insert a new furniture item, it will save same id, price to product table as well.

Why you would use inheritance for table?

Some real life examples is having transaction table in eCommerce website and then you have different transaction data for each payment type. One solution is to create table for each payment provider transaction but then you’ll have the following problems:
– To create a report about transactions, you’ll need to hard-code each table in the query.
– For each new table, data consistency is vulnerable to designer error. There is no way to enforce consistency.

What are available types of inheritance?

There are two types of inheritance in doctrine
1. Single Table Inheritance.
Where all data types are encapsulated in one table and discriminated by a field.
2. Joined Table Inheritance.
Where each type has its own table and data are joined between super table. See example later in this post.

No more talks … show me the code.

The example in this post will be a product table and sub tables about specific categories. Each product regardless the category will share some characteristics like id, name, price, image_url, created_at and updated_at but each category will have its own special requirements. For example, furniture will have dimensions (width, height, length, weight) and brand name. Food will have weight, expiry_date and online downloadable item will have download_url.

We are going to use Joined table inheritance method so we can have a separate table for each category, in this way you’ll be able to add/remove categories easily without affecting the main super table.

Product Table Yaml

Entity\Product:
 type: entity
 table: product
 inheritanceType: JOINED
 discriminatorColumn:
   name: product_category
   type: string
 discriminatorMap:
   Furniture: FurnitureProduct
   Food: FoodProduct
   Online: OnlineProduct
 id:
   id:
     name: id
     type: guid
     length: 36
     nullable: false
     options:
       fixed: true
     generator:
       strategy: UUID
 fields:
   name:
     column: name
     type: string
     length: 255
     nullable: false

   price:
     column: amount
     type: decimal
     precision: 10
     scale: 2
     nullable: false

   imageUrl:
     column: image_url
     type: string
     length: 255
     nullable: true

   createdAt:
     column: created_at
     type: datetime
     nullable: false

   updatedAt:
     column: updated_at
     type: datetime
     nullable: false

Furniture Product Table Yaml

Entity\FurnitureProduct:
 type: entity
 table: furniture_product

 fields:
   width:
     column: width
     type: decimal
     nullable: true

   height:
     column: height
     type: decimal
     nullable: true

   length:
     column: length
     type: decimal
     nullable: true

   weight:
     column: weight
     type: decimal
     nullable: true

   brand:
     column: brand
     type: string
     length: 255
     nullable: true

Food Product Table Yaml

Entity\FoodProduct:
 type: entity
 table: food_product

 fields:

   expiryDate:
     column: expiry_date
     type: datetime
     nullable: false

   weight:
     column: weight
     type: decimal
     nullable: true

Online Product Table Yaml

Entity\OnlineProduct:
 type: entity
 table: online_product

 fields:

   downloadUrl:
     column: download_url
     type: string
     length: 255
     nullable: false

   downloads:
     column: downloads
     type: int
     nullable: false

Now how do these entities connect to each others? Simple … here is the php code. Please note that each sub class will extend the super class (Product).

<?php
namespace Entity;

abstract class Product
{
    /**
     * @var guid 
     */
    protected $id;
    /**
     * @var string
     */
    protected $name;
    /**
     * @var float
     */
    protected $price;
    /**
     * @var string
     */
    protected $imageUrl;
    /**
     * @var \DateTime
     */
    protected $createdAt;
    /**
     * @var \DateTime
     */
    protected $updatedAt;

    /**
     * @return GUID
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @param GUID $id
     */
    public function setId($id)
    {
        $this->id = $id;
    }

    /**
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param string $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * @return float
     */
    public function getPrice()
    {
        return $this->price;
    }

    /**
     * @param float $price
     */
    public function setPrice($price)
    {
        $this->price = $price;
    }

    /**
     * @return string
     */
    public function getImageUrl()
    {
        return $this->imageUrl;
    }

    /**
     * @param string $imageUrl
     */
    public function setImageUrl($imageUrl)
    {
        $this->imageUrl = $imageUrl;
    }

    /**
     * @return \DateTime
     */
    public function getCreatedAt()
    {
        return $this->createdAt;
    }

    /**
     * @param \DateTime $createdAt
     */
    public function setCreatedAt($createdAt)
    {
        $this->createdAt = $createdAt;
    }

    /**
     * @return \DateTime
     */
    public function getUpdatedAt()
    {
        return $this->updatedAt;
    }

    /**
     * @param \DateTime $updatedAt
     */
    public function setUpdatedAt($updatedAt)
    {
        $this->updatedAt = $updatedAt;
    }

}

Now lets have a look at Furniture Product table

<?php

namespace Entity;

/**
 * Class FurnitureProduct
 * @package Entity
 */
class FurnitureProduct extends Product 
{
    /**
     * @var float
     */
    protected $width;
    /**
     * @var float
     */
    protected $height;

    /**
     * @var float
     */
    protected $length;
    /**
     * @var float
     */
    protected $weight;
    /**
     * @var string
     */
    protected $brand;

    /**
     * @return float
     */
    public function getWidth()
    {
        return $this->width;
    }

    /**
     * @param float $width
     */
    public function setWidth($width)
    {
        $this->width = $width;
    }

    /**
     * @return float
     */
    public function getHeight()
    {
        return $this->height;
    }

    /**
     * @param float $height
     */
    public function setHeight($height)
    {
        $this->height = $height;
    }

    /**
     * @return float
     */
    public function getLength()
    {
        return $this->length;
    }

    /**
     * @param float $length
     */
    public function setLength($length)
    {
        $this->length = $length;
    }

    /**
     * @return float
     */
    public function getWeight()
    {
        return $this->weight;
    }

    /**
     * @param float $weight
     */
    public function setWeight($weight)
    {
        $this->weight = $weight;
    }

    /**
     * @return string
     */
    public function getBrand()
    {
        return $this->brand;
    }

    /**
     * @param string $brand
     */
    public function setBrand($brand)
    {
        $this->brand = $brand;
    }
}

Same with Food Product

<?php

namespace Entity;


/**
 * Class FoodProduct
 * @package Entity
 */
class FoodProduct extends Product
{
    /**
     * @var \DateTime
     */
    protected $expiryDate;
    /**
     * @var float
     */
    protected $weight;

    /**
     * @return \DateTime
     */
    public function getExpiryDate()
    {
        return $this->expiryDate;
    }

    /**
     * @param \DateTime $expiryDate
     */
    public function setExpiryDate($expiryDate)
    {
        $this->expiryDate = $expiryDate;
    }

    /**
     * @return float
     */
    public function getWeight()
    {
        return $this->weight;
    }

    /**
     * @param float $weight
     */
    public function setWeight($weight)
    {
        $this->weight = $weight;
    }
}

And same with Online Product

<?php

namespace Entity;


/**
 * Class OnlineProduct
 * @package Entity
 */
class OnlineProduct extends Product
{
    /**
     * @var string
     */
    protected $downloadUrl;
    /**
     * @var integer
     */
    protected $downloads;

    /**
     * @return string
     */
    public function getDownloadUrl()
    {
        return $this->downloadUrl;
    }

    /**
     * @param string $downloadUrl
     */
    public function setDownloadUrl($downloadUrl)
    {
        $this->downloadUrl = $downloadUrl;
    }

    /**
     * @return int
     */
    public function getDownloads()
    {
        return $this->downloads;
    }

    /**
     * @param int $downloads
     */
    public function setDownloads($downloads)
    {
        $this->downloads = $downloads;
    }

}

How does that work in the database?

Every time you insert (persist) a new record in each of sub tables (entities) (FurnitureProduct, FoodProduct or OnlineProduct) it will insert the mutual fields in a table called product.

PLEASE NOTE: You don’t insert into the super class product

Any questions? Please let me know in the comments section.

Total Views: 66 ,