Many-to-many Database Relation in Yii

I have three database tables:

Phonebook - employee contact info.
Council - members of the Unit that are in the advisory council.
Offices - titles of all offices in the council

Council has fields for an employee id and the year of council service (and a primary key).
Offices are titles (and a primary key).
Phonebook has a field for employee id (and a primary key). It also has fields such as name, address, phone.

An index table, council_offices, has been created to link Council to Offices. A person on the council can hold many offices. Several people can have the same title. It has a field for the Council primary key and a field for the Offices primary key (and a primary key). A relation has been created for each field.

Each table is represented as a model in Yii. The relations method is of importance here. The Offices table does not have any relations.

CouncilOffices relations

Each record in the CouncilOffices table has a many-to-one relationship with the Council and Offices tables. Council and Offices are related as many-to-many. The way to implement this is with two many-to-one relationships through an intermediate table.

BELONGS-TO indicates that CouncilOffices is the many side and Council is the one side of the relation. The council_index field links to the primary field of the Council table. Create a foreign key in the council_offices table that links to the primary key from the council table.

A similar relationship exists for Offices.

        return array(
                    'councilRelation'=>array(self::BELONGS_TO, 'Council', 'council_index'),
                    'officesRelation'=>array(self::BELONGS_TO, 'Offices', 'offices_index'),
        );
   
Council relations

Council defines the many-to-many relationship to Offices. Offices does not define any relations. The third parameter defines how the relationship works: It uses the council_offices table, tying the council_index to the offices_index. These names are the actual names from the table, including the table name and field names. It is confusing that Offices is the name of the Yii class for the model, while council_offices is the actual name of the table in the database.

Council also defines a relationship to the phonebook. It should actually be one-to-one, but one-to-many will also work.

        return array(
                    'officeRelation'=>;array(self::MANY_MANY, 'Offices',
                        'council_offices(council_index,offices_index)',
                        ),
                    'phonebookRelation'=>;array(self::BELONGS_TO, 'Phonebook',
                        'id',
                        ),
        );

Create a foreign key from the id field of the council table to the phonebook table. In my case, the foreign key is to an index, not to a primary key. I am using mySql with INNODB tables. Yii does not specify this relationship, it only exists in the actual table. Create an index for the target field in the phonebook table and then use SQL to create the foreign key (phpMyAdmin only allows foreign keys to primary keys).

alter table council add foreign key (id) references phonebook(pid);

Phonebook relations

The Phonebook does not need any relations in Yii. It should have an index for the empolyee id (pid) in the phonebook table.

Form that accesses data

The payoff is accessing the relations. In the example below, $data is the CouncilOffices table.