Social network comments and posts model in PostgreSQL 9.6
First the author table, that's easy.

CREATE TABLE author (
    authorid   serial  PRIMARY KEY,
    username   text    UNIQUE
);


Now we use a self-referential post table to create the hierarchical structure.

CREATE TABLE posts (
    id           serial  PRIMARY KEY,
    parent_post  int     REFERENCES posts,
    authorid     int     NOT NULL REFERENCES author,
    body         text    NOT NULL
);


Test data...

INSERT INTO author (authorid,username) VALUES
  (0, 'ecarroll'),
  (1, 'jbob');
INSERT INTO posts (id,parent_post,authorid,body) VALUES
  (0, null, 0, 'HALLO WORLD'),
  (1 ,0,    1, 'HALLO EVAN' );


Recursive query to get it working.

WITH RECURSIVE t(chain,author_un,text,id) AS (
   SELECT ARRAY[id], username, body, id
   FROM posts
   INNER JOIN author USING (authorid)
   WHERE parent_post IS NULL
   UNION ALL
     SELECT t.chain||p.id, username, p.body, p.id
     FROM t
     INNER JOIN posts AS p
       ON (t.id = p.parent_post)
     INNER JOIN author AS a
       USING (authorid)
)
SELECT *
FROM t;

 chain | author_un |    text     | id 
-------+-----------+-------------+----
 {0}   | ecarroll  | HALLO WORLD |  0
 {0,1} | jbob      | HALLO EVAN  |  1
(2 rows)




For more information on this method, see my post here


How to build a table for a private messaging system that supports replies?