Suggestion for db structure(Yii)

Yii 1.1 General Discussion
1

I have one dilemma, regarding what would be best way to design database in order to use full power of Yii AR class.

Lets say that I have few tables: ‘article’, ‘blog’, ‘user’, with primary keys like article_id, blog_id, user_id for example.

I want to be able to post comments to each of those tables, using one comment table.

Usually I solve this problem with ref_type_id, which in this case could be enum(‘article’,‘blog’,‘user’), and ref_id, which would be foreigner key to one of mentioned three tables.

So, my guess is how I would describe this relationships in model classes? I would like to access all comments of an article using $article->comments method for example(‘comments’ should be key in relationship).

I am also think about using assignment tables, which wouldn’t have models, and in this case I would know how to describe relationships, but then I would have three additional tables, which I do not want.

So, can you suggest me how relationships in models should look like. Or maybe there is another way to solve this problem.

Thanks in advance!

2

When creating your model, and so long as your tables have foreign keys the relationships will be generated for you by Gii. Check this article out: Relational Active Record

3

But my foreigner key(I can’t define it as foreigner key in db schema) in comment table point to three other tables depending on ref_type_id value, so this need to be setup manually(if possible), gii can not create them.

4

Try:




class Article extends CActiveRecord 

  ...

  public function relations() {

    return array(

      'comments' => array(self::HAS_MANY, 'Comments', 'ref_id', 'on' => 'comments.ref_type_id = "article"'),

    );

  }

  ...
5

This might be right solution. Will try, and inform you.

6

Not the best database design… mostly because you can’t add foreign key constraints and make a cascade deletion of all related comments. Check this link: http://stackoverflow.com/questions/2002985/mysql-conditional-foreign-key-constraints

Another solution is to create 3 comment tables. It looks the most obvious and easy one to me, you won’t need to duplicate a program code, just create a base Comment class and 3 descendants of it.

IMO any of these ways is better than yours, but you decide ;)

7

Thanks on answer, you are right about previous solution, it is far from ideal.

Having one Comment base table, with few(three in my case), assignment tables is what I think about also. But problem is what if there are 10 tables on which I will allow comments, I will have 10 additional tables, what I don’t want.

However, link you sent me is good idea for solution of my problem. If I all classes(tables) that can be commented extend base class(table), and primary key of this base class is foreigner key in all comments, it would work on db level.

So, this db structure is called Polymorphic Associations. Does Yii Active Record supports it?

8

No, Polymorphic Associations is what you’ve described in the very first message, because you was trying to associate a one table with many others depending on a field value.

ActiveRecord doesn’t have a built-in functionality for handling a database structure described by the link, but all you need to do is to insert a new row into the “Commentable” table and assign a foreign key. Probably beforeSave() is a good place to do it.

There is no another way if you want your tables to support foreign keys constaints. With this database structure you will also have 10 tables + 1 master table, but you can put all common fields into the Commentable table (author, text, date etc). All other tables will just have foreign keys refering to Articles, Blogs etc.

This db structure looks better when you have really many tables with many common fields and also with some unique fields in each table.

Creating 10 tables doesn’t look a bad solution to me. There are no any problems with it. I would choose this way and enjoy it’s lightness and clarity :D

And one more way (don’t know if it’s bad or very bad):

[ comment_id | author | text | date | article_id | blog_id | user_id ]

In each row two foreign keys will have NULL values, but still it looks a better solution than your first one.

9

Well, I am in big dilemma, and very close to decide for http://stackoverflow.com/questions/2002985/mysql-conditional-foreign-key-constraints db structure. This is also good solution in case I have few more tables similar to comment table(for example rate and attachment table, where we could allow users to rate on articles, blogs), but still thinking about first solution(to have ref_type_id field in comment table) and to modify model class function, so in model class I would delete referenced table data, instead it to by done by db(foreigner keys).

10

I faced the same problem a while ago

this link might help you too

In my case, I go for the "base parent table" concept most of the time

EDIT: on slide 22 he starts to talk about Polymorphic Associations, but I recommend you to read the whole thing when you have time

11

I hope you won’t end up with a one “master” table for all other tables :lol: If two or more tables have some similar or equal fields, it doesn’t mean we should put everything into 1 table. It can also decrease the performance. That’s why no one creates a one table for everything. All db structures described above have their bottlenecks and none of them is perfect. But let’s face the truth, the only problem with 10 comment tables is their number, but tables are created only once and DBMS has less problems handling many simple tables.

Actually I don’t know how fast will a database work with all of these db designs on a big amount of data. Separate tables look very scalable, but maybe some one there had an experience with this and is ready to share with us ::)

12

I spend few hours yesterday, thinking about problem, and searching for suggestions.

The problem with master table is that few tables that have nothing in common ‘extends’ this table, for example ‘user’ and ‘blog’ tables. There are other reasons too, so I will not go with this solution for sure.

Still considering your idea to have separate comment tables for each comment-able table, I could have one base AR class for all comment tables, and class for every comment table that will extend it. But more probably is that I will use first solution I thought - to have ref_type field, and to programmatically maintain all referenced updates/deletes. From db administrator view, it might not be the best solution, but from application level, it will be.

And I will be able to easy generate reports, like total user comments, which would be a bit more complex, using separate comment tables(more queries).