Comments for multiple parent objects

 

I'm learning to build web-apps in the Yii PHP framework, but I think my question applies to web-applications in general.

I want to know how to structure database tables for comments that relate to different types of parent objects, ie- photos or posts.

Photos and Posts are inherently different and wouldn't sit in the same table. However the comments on these objects are in most cases of identical format.

My question is two fold, is it possible to put comments for multiple parent objects into the same table? [Seems to me to be the natural solution, but I'm not sure how to take care of this with foreign/primary keys etc. I think it also needs a key to point towards the type of parent object...?]

Second, if it is possible to put these comments into the same table, is it efficient to do so, is it more efficient to have a comment table for every parent object?

Thanks in advance, hope it's an interesting enough topic for someone.

Cheers,

Nick

The other option is to have a post table to which all posts relate (regardless of type). So whether it's a photo or a normal post, it will have a records created for it in this table.

Depending on the type of content, you could store both photos and normal posts in this table (with optional fields for each, eg: photo, photo_type, post_content could be optional depending on what type is being created (this would be a good opportunity to learn about Yii model validation scenarios).

So you post table would look something like:

  • id
  • post_content
  • photo
  • photo_type
  • etc...

    • If you choose not to store both in the same table, you could have 1 table for photos and 1 for posts which relate back to a post_id

    That way, you could have a single comment table (which regardless is the correct route) which relate to a post_id.

    I would model Comment as a table on it's own. The Comment table would have a columns for PhotoId and PostId. These are foreign keys that allow nulls. Then a database check constraint would ensure that exactly one of PhotoId and PostId is null. ie each comment is attached to exactly one parent.

    I can suggest 3 table structure

    Table: [Entity_Types] (List all object titles such as photos, posts) Example: PK:1001, "Photos"

    Table: [Photos] Example: PK:2001, "My 1st Photo Title" PK:2002, "My 2nd Photo Title"

    Table: [Comments] Examples: PK:3001, "My 1st Comment for My 1st Photo", FK:1001, FK:2001 PK:3002, "My 2nd Comment for My 1st Photo", FK:1001, FK:2001 PK:3003, "My 1st Comment for My 2nd Photo", FK:1001, FK:2002

    链接地址: http://www.djcxy.com/p/70626.html

    上一篇: 基础故事分享网站的数据库设计

    下一篇: 多个父对象的注释