Database Design : Join Table Vs Type Field

Yii 1.1 General Discussion
1

I’m looking insight into designing a database with a table which is related to multiple other tables. A specific example is a site with comments on different kinds of the things - people could comment on articles, or they could comment on photos or they could comment on people.

It seems that there are two ways to represent this:

  1. join tables for each other table

TABLES:

  • articles

  • articles_comments

  • comments

  • comments_people

  • comments_photos

  • people

  • photos

or

  • articles

  • comments

  • people

  • photos

and the comment table will have a "type" field and a item_id to link back into the other table.

The first approach seems more "proper" and it seems we should have no problem using foreign key restraints whereas the second approach has fewer tables and might be "simpler" in some respect, but we can use FK restraints since the item_id can related to multiple FKs (AFAIK - using mysql innodb). It might be good to not that in our application there might be 2-3 tables that have multiple relations (comments, photos, etc) and 5-10 tables that need relations.

I’m looking for advice on which is the better approach.

2

Instead join tables I would concider

  • articles

  • articles_comments

  • people

  • people_comments

  • photos

  • photos_comments

where *_comment tables structure is identical except foreign key.

This way you can add its parent related fields if needed.

In both cases I would have a model called BaseComment and 3 other models that all extend this one and overload the tableName function.


class ArticleComment extends BaseComment

{

	public function tableName()

	{

		return '{{articles_comments}}';

	}


	public function relations()

	{

		return array(

			// this could probabluy be abstracted into baseclass too

			'parent' => array(self::BELONGS_TO, 'Article', 'parent_id'), 

		);

	}

}
3

Good advice above.

4

I would try and unify some of that.

Have a master table that has unique primary keys

TABLE: contents

id: INT

type: INT

TABLE: articles

id: INT -> FK to contents

TABLE: people

id: INT -> FK to contents

TABLE: comments

id: INT

owner: INT -> FK to contents

Would be simpler to have one comments table from an admin perspective, can then be filtered by type.

5

i prefer using join table, its more flexible because if the table design changed it will need to edit many table, in controller it will need a lot more code, also its easier if i want to display these *_comment table in 1 controller action or in view it will need lot more code. For big project using this approach it will take much more time to maintaining the code