{"issue":{"created_at":"2011-11-14T07:45:39Z","state":"open","updated_at":"2011-11-19T22:00:58Z","user":{"gravatar_id":"4097134b478d395b164b407bb8a03bf9","id":1192893,"url":"https://api.github.com/users/jstewmc","login":"jstewmc","avatar_url":"https://secure.gravatar.com/avatar/4097134b478d395b164b407bb8a03bf9?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png"},"comments":2,"body":"Many times entities are types of other entities. The different types share common attributes, but they also have their own individual attributes. \r\n\r\nFor 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. \r\n\r\nThe 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.\r\n\r\nIt'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)?\r\n\r\n```SQL\r\nCREATE TABLE people\r\n`id` int\r\n`person_type` varchar(8)  -- discriminator column, possible values are 'employee' or 'customer'\r\n`name` varchar(64)\r\n`email` varchar(256)\r\n`street address` varchar(256)\r\n`gender` char(1)\r\n`phone` char(10)\r\nPRIMARY KEY `id`\r\n\r\nCREATE TABLE person_employees\r\n`person_id` int\r\n`office_number` tinyint\r\n`director_name` int\r\n`salary` int\r\nPRIMARY KEY `person_id`\r\n\r\nCREATE TABLE person_customers\r\n`person_id` int\r\n`credit_card_number` bigint\r\n`last_purchase` datetime\r\n`first_purchase` datetime\r\nPRIMARY KEY `person_id`\r\n```\r\n\r\n```PHP\r\nclass Person extends DataMapper {\r\n    /**\r\n     * Define super- to sub-type relationship.\r\n     *\r\n     * This array has two elements. The first element is an array of sub-types, indexed by \r\n     * discriminator value. The second element is the name of the discriminator column.\r\n     *\r\n     */\r\n    $can_be = array(\r\n        array(\r\n            'employee' => array(\r\n                'model_name' => 'Employee',  // name of sub-type's model\r\n                'join_key' => 'person_id',  // column that relates super and sub-types\r\n                'join_table_name' => 'person_employees'  // name of subtype table\r\n            ),\r\n            'customer' => array(\r\n                'model_name' => 'Customer',\r\n                'join_key' => 'person_id',\r\n                'join_table_name' => 'person_employees'\r\n            )\r\n        ),\r\n        'person_type'\r\n    );\r\n}\r\n\r\nclass Employee extends Person {\r\n    /**\r\n     * Define sub- to super-type relationsihp.\r\n     *\r\n     * This array has three elements: the name of the super-type's model, the name of \r\n     * the super-type's id, and the name of the super-type table. This setup is simpler \r\n     * than the super-type's because each sub-type can only be a sub-type of one super-type.\r\n     *\r\n     */\r\n    $primary_key = 'person_id';    \r\n    $is_a = array(\r\n        'model_name' => 'Person',\r\n        'join_key' => 'id',\r\n        'join_table_name' => 'People'\r\n    );\r\n}\r\n```\r\nWith those relationships defined...\r\n* 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.\r\n* On insert, DM would insert a super-type record and it's corresponding sub-type record.\r\n* On delete, DM would delete both the super-type and sub-type record.\r\n\r\nI 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.\r\n","assignee":null,"id":2227862,"pull_request":{"patch_url":null,"html_url":null,"diff_url":null},"number":11,"labels":[{"name":"Awaiting feedback","color":"DDDDDD","url":"https://api.github.com/repos/WanWizard/Datamapper2/labels/Awaiting+feedback"},{"name":"Feature","color":"0b02e1","url":"https://api.github.com/repos/WanWizard/Datamapper2/labels/Feature"},{"name":"Pending","color":"d7e102","url":"https://api.github.com/repos/WanWizard/Datamapper2/labels/Pending"}],"title":"Implement super- and sub-type table support","closed_at":null,"html_url":"https://github.com/WanWizard/Datamapper2/issues/11","milestone":null,"url":"https://api.github.com/repos/WanWizard/Datamapper2/issues/11"},"action":"created","comment":{"created_at":"2011-11-19T22:00:58Z","updated_at":"2011-11-19T22:00:58Z","user":{"gravatar_id":"4097134b478d395b164b407bb8a03bf9","id":1192893,"url":"https://api.github.com/users/jstewmc","login":"jstewmc","avatar_url":"https://secure.gravatar.com/avatar/4097134b478d395b164b407bb8a03bf9?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png"},"body":"I'd envisioned it as an actual merge. \r\n\r\n```php\r\n// get employee number 1\r\n$employee = new Employee();\r\n$employee->get_by_id(1);\r\n// for an attribute stored in \"employee\" table\r\necho $employee->fieldname;\r\n// for an attribute stored in \"persons\" table\r\necho $employee->fieldname;\r\n```\r\nHowever, 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. \r\n\r\nI think your has_one/auto_populate is the best solution. You suggested a similar solution in your [CI forum answer](http://codeigniter.com/forums/viewthread/203944/), but I just didn't get it. The same suggestion was given on a [StackOverflow answer](http://stackoverflow.com/questions/8062438/how-do-i-setup-super-and-sub-type-relationships-in-codeignitors-datamapper-orm) (Sorry for the cross-post, I kind of deadline-panicked. I thought I might reach a wider audience on StackOverflow).\r\n\r\nAnyway, you should probably close this issue as wont_fix, too_complicated, other_solutions_available, nobody_else_needs_it haha.","id":2801166,"url":"https://api.github.com/repos/WanWizard/Datamapper2/issues/comments/2801166"}}