Implement super- and sub-type table support

[WanWizard]

Requested by @jstewmc

Many times entities are types of other entities. The different types share common attributes, but they also have their own individual attributes.

For example, both employees and customers are people. As people, employees and customers have a name, phone number, email, address, gender, etc. However, employees have a director, a department, a salary, and an office number too. Customers, on the other hand, have several credit card numbers, a last-purchase-date, a first-purchase-date, and more. Instead of maintaining two tables with many identical columns (or one table with lots of NULL columns), a common practice in database design is to create three tables with a super- / sub-type relationship.

The super-type table people has an id column, a type column, and columns for all the attributes that people have in common (name, phone number, email, address, gender, etc.). The sub-type tables, person_employees and person_customers, use person_id as their primary key, and they have columns for the attributes specific to their type.

It'd be great if DM ORM supported this type of relationship. Maybe something like this (I don't know enough about DM's syntax, but hopefully you get the idea)?

CREATE TABLE people
`id` int
`person_type` varchar(8)  -- discriminator column, possible values are 'employee' or 'customer'
`name` varchar(64)
`email` varchar(256)
`street address` varchar(256)
`gender` char(1)
`phone` char(10)
PRIMARY KEY `id`

CREATE TABLE person_employees
`person_id` int
`office_number` tinyint
`director_name` int
`salary` int
PRIMARY KEY `person_id`

CREATE TABLE person_customers
`person_id` int
`credit_card_number` bigint
`last_purchase` datetime
`first_purchase` datetime
PRIMARY KEY `person_id`

class Person extends DataMapper {
    /**
     * Define super- to sub-type relationship.
     *
     * This array has two elements. The first element is an array of sub-types, indexed by 
     * discriminator value. The second element is the name of the discriminator column.
     *
     */
    $can_be = array(
        array(
            'employee' => array(
                'model_name' => 'Employee',  // name of sub-type's model
                'join_key' => 'person_id',  // column that relates super and sub-types
                'join_table_name' => 'person_employees'  // name of subtype table
            ),
            'customer' => array(
                'model_name' => 'Customer',
                'join_key' => 'person_id',
                'join_table_name' => 'person_employees'
            )
        ),
        'person_type'
    );
}

class Employee extends Person {
    /**
     * Define sub- to super-type relationsihp.
     *
     * This array has three elements: the name of the super-type's model, the name of 
     * the super-type's id, and the name of the super-type table. This setup is simpler 
     * than the super-type's because each sub-type can only be a sub-type of one super-type.
     *
     */
    $primary_key = 'person_id';    
    $is_a = array(
        'model_name' => 'Person',
        'join_key' => 'id',
        'join_table_name' => 'People'
    );
}

With those relationships defined...

• On select, DM would return one object with data from both tables. For example, a selected Employee instance would not only have office number, salary, and director_name properties, but it'd also have properties for person_type, name, email, street address, sex, and phone number.
• On insert, DM would insert a super-type record and it's corresponding sub-type record.
• On delete, DM would delete both the super-type and sub-type record.

I know it's pretty complicated, it might never have been requested before, and it's stretching the ActiveRecord implementation of ORM, but I think it would be a nice feature.

[WanWizard]

How would you access the data in the object?

// for person info
echo $person->fieldname;

// for employee info
echo $person->employee->fieldname

Or do you see it as an actual merge, where the object has to have some form of tracking which field comes from which table? This might become very complicated.

Assuming there is a one-one relation between the "super" and "sub" tables, you might even be able to do this today by defining the relations as "has_one", and use auto_populate=TRUE on the relation to automatically fetch them when you fetch a person record.

You can then do

// get a person
$person = new Person();
$person->get_by_id(1);

// check the subtypes
if ( $person->employee->exists() )
{
    // this person is an employee
}
if ( $person->customer->exists() )
{
    // this person is an customer
}

In this case you won't even need the discriminator field. Thats also why I didn't use elseif in this example, a person could be an empoyee AND a customer (which is not supported in your proposal).

I don't know from the top of my head if the current release would save updates to customer or employee if you save the person, but that could be part of version 2 if not.

[WanWizard]

Response by @jstewmc

I'd envisioned it as an actual merge.

// get employee number 1
$employee = new Employee();
$employee->get_by_id(1);
// for an attribute stored in "employee" table
echo $employee->fieldname;
// for an attribute stored in "persons" table
echo $employee->fieldname;

However, having learned a bit more about how ActiveRecord ORM's work, I can appreciate how incredibly hard that would be to implement. Throw in a few relationships for an employee (like one-employee-to-many-projects or one-employee-to-many-customers-helped), and things get even more complicated. I guess designing an omnipotent ORM can be a little difficult haha.

I think your has_one/auto_populate is the best solution. You suggested a similar solution in your CI forum answer, but I just didn't get it. The same suggestion was given on a StackOverflow answer (Sorry for the cross-post, I kind of deadline-panicked. I thought I might reach a wider audience on StackOverflow).

Anyway, you should probably close this issue as wont_fix, too_complicated, other_solutions_available, nobody_else_needs_it haha.

[WanWizard]

See also #8