Trouble with my relations

Say the schema looks like this:



  -- basic user info columns

  PRIMARY KEY (`id`)

) ENGINE = InnoDB;

CREATE TABLE `user_profile` (


  `name` VARCHAR(200),

  -- other general user profile columns

  PRIMARY KEY (`user_id`) ,

  CONSTRAINT `fk_user_profile_user1`

    FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)


) ENGINE = InnoDB;

CREATE TABLE `client` (


  -- client-specific profile columns

  PRIMARY KEY (`user_id`),

  CONSTRAINT `fk_client_user1`

    FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)


) ENGINE = InnoDB;

In which user_profile has an identifying one-to-one relation to user and client has an identifying one-to-one relation to user. Not all users have a user profile. Not all users are clients. All clients have a user profile (business logic).

I want to be able to access user profile fields from a client model like this:

$client = Client::model->findByPk($id);

echo $client->userProfile->name;

instead of having to use:

echo $client->user->userProfile->name;

But I’m having trouble writing my relationship rules. I wasn’t able to figure the answer from “Relational Active Record” in The Definitive Guide or either of the Yii books. This doesn’t work:

public function relations() {

  return array(

    'user' => array(self::BELONGS_TO, 'User', 'user_id', 'joinType'=>'INNER JOIN'),

    'userProfile' => array(self::HAS_ONE, 'UserProfile', 'user_id'),



And neither does:

    'userProfile' => array(self::HAS_ONE, 'UserProfile', 'user_id', 'through'=>'user'),


    'userProfile' => array(self::HAS_ONE, 'UserProfile', 'id', 'through'=>'user'),

As you can see, I’m guessing now.

Any idea would be very welcome.

There is no reason for you to use ‘through’. And i really don’t get the difference between ‘basic user info columns’ and ‘user_profile’. And if ‘user_profile’ and ‘client’ have one-to-one relationship, why did you separate those into two tables? You might want to take a re-look into your table design.

Anyways, as for your problem, first of all you should change your client table such that it has -

‘id’ -> primary id for the table

‘user_profile_id’ -> foreign key referencing the primary key in ‘user_profile’ table.

    or the other way round (as per your requirement). 

and then take a look at this wiki for clarification on HAS_ONE versus BELONGS_TO -

I thought not too, as shown in my first example.

Basic user info columns are data all users must have. But not all users have user profile data. Moreover, not all users are clients.

The client-user relation, f : CU is an injective, non-surjective function. Every client has exactly one user (in this sense it is one to one) but every user has at most one client (i.e. zero or one). The profile→user relation is the same kind of function.

If all users were clients with profiles then I’d just have one table: client. As it happens I have several kinds of user (one of these kinds being client), and each kind has its distinct set of fields. Some of the kinds of user require the common user profile fields and others do not.

The Wiki article seems only to deal with non-identifying relations.

In my example, the relation from client to user is identifying, as is the relation from user_profile to user. A user can exist without a profile but a profile cannot exist without a user. Hence the FK in user_profile is part of the table’s PK. Since there is at most one profile per user, user_profile needs nothing else in its PK. (And similarly for the client to user relation.)

The Wiki article’s schema has the profile table designed so that profiles can exist without belonging to users. Its profile table’s PK id is independent of the FK to user. This is a non-identifying relation: as far as this schema is concerned, a profile can be divorced from its user. To make the relation identifying so that a profile has to have a user, the profile table’s PK would be PRIMARY KEY (id, user_id). But now, unless there’s a need for more than one profile per user, the profile table’s independent id column is superfluous.

Can Yii provide the relation I’m looking for without requiring redundant IDs as Mukesh proposed?