bonniss26
bonniss26

I have a schema, in which USER is in a many-to-many relationship with TITLE through DEGREE:

USER -|--------||-> DEGREE <-||--------|- TITLE
--
# USER
-------------------
| id | user_name  |
|----|------------|
| 1  | Anthony    |
| 2  | Linda      |

# TITLE
-------------------
| id | title_name |
|----|------------|
| 1  | bachelor   |
| 2  | professor  |

# DEGREE
primaryKey:[user_id, title_id, major, university]
---------------------------------------------------------------------
| user_id | title_id   | major      | university        | year      |
|---------|------------|------------|-------------------|-----------|
| 1       | 2          |biology     | florida           |   2012    |
| 2       | 1          |math        | seoul             |   NULL    |
| 2       | 1          |physics     | tokyo             |   NULL    |
| 1       | 2          |biology     | hanoi             |   2009    |

As you can see, the primary key of DEGREE is a 4-tuple. Therefore, as long as the value combination of these primary key fields is unique, it's a valid record. I have used the pivot table and had a proper look to the test plugin of OctoberCMS. I manage to integrate a relation partial of DEGREE into the form of creating a new USER.

/* Models\User.php */
public $belongsToMany = [
    /*...*/
    'degrees' => [
        'Xyz\Xyz_plugin\Models\Title',
        'table' => 'xyz_degree',
        'key' => 'user_id',
        'otherKey' => 'title_id',
        'pivot' => ['major', 'university, 'year'],
        'pivotModel' => 'Xyz\Xyz_plugin\Models\Degree'      // optional
    ]
];

/* Models\Degree.php */
<?php namespace Xyz\Xyz_plugin\Models;
use October\Rain\Database\Pivot;
use Model;
class Degree extends Pivot
{
    use \October\Rain\Database\Traits\Validation;

    public $rules = [];
}

After adding the forms and columns template for DEGREE and configure in USER controller, the DEGREE relation partial showed up in the USER form. I can create the USER with DEGREE. But the problem is October did not detect and it treats the model of DEGREE in a manner of 2-tuple-primary key of [user_id, title_id]: I cannot add two DEGREE with the same title, e.g Bachelor, because it's omitted in the TITLE list after I create the first DEGREE. I followed neatly to the code of Users in the test plugin.

I have try another thinking as restructuring the schema to: USER has many DEGREE, and TITLE has many DEGREE. The problem with that is must add an Id field to DEGREE, while InnoDB does not allow an auto-incrementing field in a composite key. Struggling for a while, I want to know what is the proper solution for this kind of schema? I believe this is common problem but I do not get a good enough result in Google. Any help is appreciated.

1-1 of 1