Dynamics relations for comments and others generic tables

Need Help
1

Hi,
To link generic tables together, I’m used to using this type of relations.
By generic tables understand : comments, statistics, issues, etc.

create table comments
(
    id          int  not null primary key,
    ref         varchar(32)  not null primary key,
    ref_id      int  not null primary key,
    content     text  not null,
);

add index `ref` (`ref`, `ref_id`)

create table articles
(
    id          int  not null primary key,
    content     text  not null,
);

create table products
(
    id          int  not null primary key,
    name        varchar(128) not null,
);

The hasMany relation is done with ref and ref_id. The ref field refer to model name.

Some websites said : this is a lack of performance, not a good practices, …
They recommends this style of relations:

create table comments
(
    id          int  not null primary key,
    content     text  not null,
);

create table articles
(
    id          int  not null primary key,
    content     text  not null,
);

create table article_comments
(
    article_id     int  not null,
    comment_id     int  not null,
);

create table products
(
    id          int  not null primary key,
    name        varchar(128) not null,
);

create table product_comments
(
    product_id     int  not null,
    comment_id     int  not null,
);

This, required belongsToMany relations.

What do you think about that please ?
Thanks.

2

Your use of tables with ref and ref_id is perfectly fine. That’s a common way of doing polymorphic associations. Just index the 2 columns properly and you are golden.

The alternative should be used only when you in fact need many to many association. But if you just need one to many then the 1st way is totally fine.

3

Thanks, Polymorphic was the term i forgot. This kinds or relations are efficient.

I saw some videos about polymorphism in Laravel, they have specials methods for that

And this video is a good example of reusable Trait to configure relations (hasTags, hasComments, …)

I’ve not said CakePHP should copy Laravel, maybe it’s a good way or overkill.

Thanks @ADmad

4

Yes I have seen that in Laravel. In Cake all it takes is setting the hasMany association with conditions, so perhaps just needs some documentation on how to setup polymorphic associations.

5

ok, i can start a draft in documentation.

6

If I wanted the comments to be linked to both articles & products, I would have done it like this

create table comments
(
	id			int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	article_id	int unsigned default null,
	product_id	int unsigned default null,
	content		text not null,

	FOREIGN KEY article_key ( article_id ) REFERENCES articles(id),
	FOREIGN KEY product_key ( product_id ) REFERENCES products(id),
	// modified & created ? 

);
// Ensure validation in src/Modal/Table/CommentsTable.php is correct

cakephp will automatically create the src/Modal/Table/CommentsTable.php with bake command with belongsTo

7

This is a way but if you have a “likes” table and propose to like, articles, comments, products, pictures, videos, artists, … tons of columns in your table.

8

I am just throwing some ideas. Not sure whether this is the best practice, but if there are lots of tables (linking to) using comments-table, I would link them with a field, such as related_table_type and in tables,

// example in src/Modal/Tables/CommentsTable.php
// Create constant. Or you can use enum on php8
const ARTICLES = 1000;


// example in src/Modal/Tables/ArticlesTable.php
$this->belongsTo('Comments', [
	'foreignKey' => 'comment_id', // Modify as needed
	'bindingKey' => 'related_id',  // Modify as needed
	 'conditions' => [
		'Comments.related_table_type' => [ CommentsTable::ARTICLES , ],               
	],
]);
9

See also

And other existing plugins for this very same topic.

The awesome list is always a good idea to take a look at regarding specific topics.