database design question

Here is my problem… I have 3 tables that hold completely different data, and I need to make it so users can post comments on each table. The main issue is I’d like to have one table that contains each of the comments (as opposed to having 3 different comment tables).

Here is an example:

The three commentable tables:

  • blogEntry

  • image

  • user

The comment table layout:

  • id

  • userId

  • targetId (this is where the problem lies, how should I specify what table the id belongs to and still have it easily manipulated within YiiFramework)

  • dateAdded

thanks in advance

Add another field into the comments table called "type"? In your comment model you add:




const TYPE_ENTRY = 1;

const TYPE_IMAGE = 2;

const TYPE_USER = 3;



I thought about that, I was also thinking about adding a nullable foreign key for each one of the tables on the comment table…

Comment Table:

id

userId

content

blogId (could be null)

imageId (could be null)

dateAdded

I use nullable foreign keys for a similar situation. This is good advice from an SQL expert (not me).

Comment(id, model, modelId, created, createdBy, text)

where model is the tableName and modelId the primary Key of the commentable Model

I use this db design below in my application with multiple content type:

Table Node

  • NodeID

  • title

  • desc

  • dateCreated

Table ContentImage

  • NodeID

  • Caption

Table ContentURL

  • NodeID

  • URL

  • Desc

Table Comment

  • NodeID

  • CommentID

  • Name

  • Email

  • Text

Above is example for 2 content type which are image and URL. So the Node table is a parent table for all content table. I use it for store meta data for Search Engine Optimization needs. So every time a content open in a page the meta tag in HTML Header for the content will get from Node table.

The Node table has auto increase ID and NodeID column in content table is a foreign key refering to NodeID in Node table. And for comment it is also has NodeID, a foreign key refering to NodeID in Node table. I use clustering index so I use NodeID and CommentID as primary key in comment table.

So every time there is a new comment it will check the NodeID of current content that commented by visitor and fill the comment table with same NodeID of commented content.

You have presented other scenario, you have one table (Node) with three 1 to 1 relationships, that is a common way to transform a conceptual ER diagram hierarchy in a logical ER diagram one. The post is referred about one table (comments) with three n to 1 one relationships.

I see two diferents approaches if you want to hold only one table of comments:

  • nullable foreign keys

  • an identificator of the target table, either being the identificator a type field or a name field, like people mentioned.

I still don’t know how to map the second alternative in Yii in any direction. But I believe remember this was possible in the Java framework Hibernate .

The first alternative is simple to map the relationships in the direction comment->other_table but not the contrary. For this, I supposse you will must to do some method (for example, getComments()) in the other models that queries the DB.

In a similar case, I created various tables to manage separately the comments of different types of entities. But it was a DB design desicion, not impossed for programming issues.