Using additional fields in MANY_TO_MANY link table

Yii 1.1 General Discussion
1

In my scenario I have Users that can belong to Domains:


User MANY_TO_MANY Domain

In each of these relationships they need additional attributes, so the link table looks like:


CREATE TABLE "user_domain" (

  "user_id"	VARCHAR NOT NULL

		CONSTRAINT fk_user REFERENCES user(id),

  "domain_id"	VARCHAR NOT NULL

		CONSTRAINT fk_domain REFERENCES domain(id),

  "role_id"	INTEGER NOT NULL

		CONSTRAINT fk_role REFERENCES role(id),

  "email"	VARCHAR NOT NULL,

  PRIMARY KEY ("user_id", "domain_id")

Examples:

user1 is Admin in domain1.com

user1 is User in domain2.com

So you can see I need to know the Role of the user in that Domain, and also what email address to use if I need to contact them.

My question is:

How should I structure the relations in the model in order to have access to the additional fields in the link table. Options are:

[list=1]

[*]Special notation in the User.php and Domain.php to indicate there are additional fields

[*]Generate a UserDomain.php model, and then have a "User ONE_TO_MANY UserDomain"

[*]Other…

[/list]

Any guidance would be appreciated…!

2

I just had a thought… I can add the multi-level relationship into the model like this:

Create a model for UserDomain.php

relations for User:




  'userDomainList' => array( self::HAS_MANY, 'UserDomain', 'domain_id', 'with'=>array('domain','role') ),

In this manner we should be able to get the UserDomainList, Domain and Role whenever we retrieve the User but I can’t seem to get past the problem in this post…

I have to resort to


<?php echo Role::model()->findByPk( $userDomain->role_id )->name ; ?>

which is just plain nasty!