Using additional fields in MANY_TO_MANY link table

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


  PRIMARY KEY ("user_id", "domain_id")


user1 is Admin in

user1 is User in

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:


[*]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"



Any guidance would be appreciated…!

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!