WanWizard/Datamapper2 — Issue #11: Implement super- and sub-type table support
Author: jstewmc
Created: 2011-11-14 07:45:39+00:00
State: open

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)?

```SQL
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`
```

```PHP
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.