Database design for posts and comments

Question

If one post has many comments, and the comments are essentially the same as posts (e.g. they have a title, pictures and audio etc.) should I create two tables or just one?

For example, if I only use one table I can have a parent_id column, so If it's not a reply to anything it would be null, otherwise, it would have the id of the parent post. On the other hand I can create a post table and a comments table. Comments can also reply back to other comment so this could get confusing quick.

  *Post*
    id
    title
    content
    image
    audio
    parent_id

or,

*Post*                        *Comments*
id                             id
title                          title
content                        content
image                          author_id 
audio                          post_id
author_id                      image
                               audio

What the second option would allow is creating indexes. Infact I won't even have to add author_id or post_id If I use indexes from the start will I?

What are you thoughts on this SO? Which would be more efficient? I thinking of using redbeanphp for this.

Attorneyatlaw

Answer 1

The second option would be better. When displaying a message board, you don't care about comments and looking them up by an indexed parent post id column is fast. Posts and comments will likely have different fields, so keeping them separate is correct. The parent id index for the first option would work fine, but conceptually, it's messy and you're basically creating an index to use on half or however many comments there are relative to posts.

Alleyn

Answer 2

As a rule in database-design: Tables are called entities, so each entity in your application should be separated and demonstrated by table. Here, although you regarded posts and comments each has the same kind of data but finally each of them is a separate entity so they should be separated in two tables. This behavior is not a personal opinion. It is basic rule that leads to more smooth application development.

Strophanthin