Yii/DB table design with "child" objects

Yii 1.1 General Discussion
1

Let’s say you have a site that stores articles of different sorts.

Let’s say some articles are review articles, some are news articles, etc.

All articles have certain attributes in common - a title, a created date, an updated date, and an author ID. However, each article sub-type also has attributes specific to their type. This makes designing a single database table that works for all article types impossible without making fields that won’t be used with every record. For example, you would want a field called rating to store a numerical rating for review articles, but that field wouldn’t be used for a news article.

In a database, there could be a table called Article, that has the fields id (PK AI), title, authorId, created, updated, and type (type would specify "review", "news", etc). Then, there could be two other tables, ArticleReview, and ArticleNews. They each have a PK field named articleId, foreign key to Article. If a new review article is posted, the generic information will be stored in the Article table, and the review-specific information is stored in ArticleReview, with a shared PK value (articleId). Same with news articles and the ArticleNews table.

I figure this is a good way to avoid redundancy, extraneous fields, and also it would make a tagging system much easier to manage with a many-to-many relationship. Just two more tables would be needed: Tag (with id and name fields) and ArticleTag (articleId, tagId fields). Then, you could easily search for all articles which are tagged with a certain tag, and specify by article type if you like using the type field in the Article table ("news", "review", etc).

Is this a legitamate way to design a database? If so, how would this be implemented using Yii? Would it be possible to make a ArticleReview model class, which extends Article, which extends CActiveRecord? Then, when I run $newsArticle->save(), somehow make it update the entries in both tables?

2

Hi TonyB,

I implemented similar structure in one of the previous projects and it works nicely.

As for save() I created additional needed model in beforeSave() method, e.g. in Article:





    protected function beforeSave() {

        if(get_class($this) !== 'Article' && $this->isNewRecord){

                $article = new self; // initiate Article object

                $article->type = $this->type; // initially save() is called on ArticleNews which has property $type = 'news'

                $article->user_id = Yii::app()->user->id;

                if ($article->insert()) 

                    $this->article_id = $article->id;

                else

             		return false;

        }

        return true;

    }

$this here is ArticleNews

$article - Article

Maybe there’s a better approach, but that’s what I implemented on that moment.

Take a look also at this article.

3

Thanks, I will probably do something similar to you, using beforeSave() to assign an PK which is unique to all content types. I figure I will do this by making a table Content with id, and contentType fields which I could use as sort of an "index".

Single Table Inheritance would be to have a single table with fields for attributes of all the various child models. Then, you use defaultScope() to load the attributes relevant to each specific model. The downside to this method is that you could end up with tables that have a LOT of columns, many of which might rarely be used. Might be more efficient from a purely performance standpoint, but just feels "sloppy" to me.

After doing some reading, it seems that Class Table Inheritance is what I have been looking for. Unfortunately, it seems like something that Yii lacks support for thus far and "work arounds" are rough at the moment: http://www.yiiframework.com/forum/index.php/topic/12978-class-table-inheritance/

I think the method I will be using is closest to Concrete Table Inheritance. This is the least "flexible" method, but it should do well for a while. Down the road when I am more experienced with Yii I can come up with a better way.

Although, as I understand it, PostgreSQL supports table inheritance. I have yet to use PostgreSQL, but it still may be the best and cleanest option available to Yii developers. Anyone have experience with Yii and PostgreSQL table inheritance?

4

After my last post I decided to give PostgreSQL a shot.

Now I’ve got a Yii app up and running with a PostgreSQL database, utilizing Class Table Inheritance. It was very easy. So far it seems to just be a matter of learning the different syntax (I’ve only used MySQL thus far).

Using pgAdmin, I made a table named Content. It has fields for generic information: id, created date, updated date, and type.

Then, I made a table named Article, which inherits Content. It has only a couple of its own fields (as of right now, just to test) body, and excerpt.

Since Article inherits Content, when using gii to create an Article model, all the attributes are automatically recognized (including the ones in Content). Thanks to the inheritance being built into PostgreSQL, as far as Yii is concerned, the Article table has all of those fields.

I just ran a test creating a new Article record. I saved it, and checked the database. Sure enough, both tables were updated. I’m glad I decided to go this route, plus it gave me a good reason to try out PostgreSQL finally.

5

Thanks, useful to know.

Hope to give it a try finally also :) with a proper project.