Linking Tables to Cakephp

Question

There are two tables in the database, posts and comments and I want to relate them.

I think I have to create a foreign key in the table comments in a field that holds the title or table id posts, so I’ll know which post that comment is from.

ALTER TABLE `comments` ADD CONSTRAINT `fk_title` 
FOREIGN KEY (`title_id`)  REFERENCES `posts` (`title`);

But I have doubts because of Cakephp’s conventions.

I can use the field id table posts, as in the following code, or should I use the title as I tried to do in the above code?

ALTER TABLE `comments` ADD CONSTRAINT `fk_post_id` FOREIGN KEY (`post_id`) 
REFERENCES `posts` (`id`);

I doubt that index fk_title too. What’s the point?

Once I’ve created the field post_id on the table comments and made the relationship below:

class Post extends AppModel {
public $hasMany = array(
    'Comment' => array(
        'className' => 'Comment'
    )
);
}

I still have to do ALTER TABLE in MYSQL SQL?

I have to do this same process of $hasMany within the class Comment (model Comment.php created for the "comments table")?

Answer 1

You should not reference the table of posts by title for the simple fact that more than one post can have the same title, causing an inconsistency in your data model.

Even if you define the title as UNIQUE, Still, it gets weird. The ideal is that both the table of posts and the table of comments have id’s, and the references between the tables are made through id’s.

Your relationship should be like this:

class Post extends AppModel {
    public $hasMany = array(
        'Comment' => array(
            'className' => 'Comment'
        )
    );
}

PS: When to the fk_title, is the reference that the table of posts makes to the comment, in the model proposed in your question.