First, about the foreign key, I think depend on the number of tables and the relationship among those tables, we will decide that using foreign key or not. In my case, I rarely use foreign key in my website database, especially with yii application. Why ?
- Make database too complex. We must check before wanna delete a record,…etc… I prefer the simple way (not use foreign key).
- Hard to update or insert new table. Imagine we have more than 20 tables which has foreign key and now we must add one or more table.
- Yii supported us with its relations.

To add relation in Yii, simple open model file and enable relation function.
For examle:

</div>
public function relations()
{
 // NOTE: you may need to adjust the relation name and the related
 // class name for the relations automatically generated below.
 return array(
  'connections' => array(self::HAS_MANY, 'Connection', 'user_id'),
  'dorms' => array(self::HAS_MANY, 'Dorm', 'user_id'),
  'partners' => array(self::HAS_MANY, 'Partner', 'user_id'),
  'userDetails' => array(self::HAS_ONE, 'UserDetail', 'user_id'),
 );

More details about yii relation.

  • BELONGS_TO : if the relationship between table A and B is one-to-many, then B belongs to A (e.g. Post belongs to User );
  • HAS_MANY : if the relationship between table A and B is one-to-many, then A has many B (e.g. User has many Post );
  • HAS_ONE : this is special case of HAS_MANY where A has at most one B (e.g. User has at most one Profile );
  • MANY_MANY : this corresponds to the many-to-many relationship in database. An associative table is needed to break a many-to-many relationship into one-to-many relationships, as most DBMS do not support many-to-many relationship directly. In our example database schema, the tbl_post_category serves for this purpose. In AR terminology, we can explain MANY_MANY as the combination of BELONGS_TO and HAS_MANY . For example, Post belongs to many Category and Category has many Post .

Example query using yii relation.

// retrieve the post whose ID is 10
$post=Post::model()->findByPk(10);
// retrieve the post's author: a relational query will be performed here
$author=$post->author;

$posts=Post::model()->with('author')->findAll();

$posts=Post::model()->with('author','categories')->findAll();

$posts=Post::model()->with(
    'author.profile',
    'author.posts',
    'categories')->findAll();

$posts=Post::model()->findAll(array(
    'with'=>array(
        'author.profile',
        'author.posts',
        'categories',
    )
));