db design question - foreign keys

Yii 1.1 General Discussion
1

Hello everyone,

I’m writing db migrations. I’m trying to decide which column to select as a foreign key from two candidates, sender_id and recipient_id, which will map to a user table, but which one is the better choice? Does it matter? Should I make 2 foreign keys here? Any input on better design practice would be appreciated.




public function safeUp()

{

  $this->createTable('message', array(

      'id' => 'pk',

      'sender_id' => 'int(11) NOT NULL',

      'recipient_id' => 'int(11) NOT NULL',

      'message' => 'varchar(8000) NOT NULL',

    ));

}


public function safeDown()

{

  $this->dropTable('tbl_message');

}
2

I’m just going to write 2 foreign keys in for now, but anyone with knowledge of best practices please send me some feedback. :)




safeUp()

{

.

.

.

$this->addForeignKey('message_sender_key', 'message', 'sender_id' 'tbl_user', 'id');

$this->addForeignKey('message_recipient_key', 'message', 'recipient_id' 'tbl_user', 'id');

}


safeDown()

{

$this->dropForeignKey('message_recipient_key', 'message');

$this->dropForeignKey('message_sender_key', 'message');

.

.

.

}
3

It looks fine to me, though I’m not an expert on db.

4

In order to preserve DB integrity, two foreign keys should be created. You will not benefit if you create only foregin key.

5

Cool, thanks for the feedback. :)