Which postgres design for a nested resource that can link to the same type of resource I have this kind of data that I need to store in postgres (ruby object). 

#&lt;InitRes:0x007f8ec3734c78
 @kind="RestrictionsSchema",
 @restrictions=
  [#&lt;ParsedRes:0x007f8ec60ed218
    @description="Disallow user",
    @key="disallowUserConfig",
    @restriction_type="bool",
    @title="Disallow user"&gt;,
   #&lt;ParsedRes:0x007f8ec2c96898
    @description="Restriction message",
    @key="disallowUserConfigMessageKO",
    @restriction_type="string",
    @title="Restriction message"&gt;,
   #&lt;ParsedRes:0x007f8ec3551730
    @description="List of configurations",
    @key="configurations",
    @nested_res=
     [#&lt;ParsedRes:0x007f8ec2565678
       @description="configuration",
       @key="configuration",
       @nested_res=
        [#&lt;ParsedRes:0x007f8ec2504328
          @description="configuration name",
          @key="name",
          @restriction_type="string",
          @title="Profile Name"&gt;,
         #&lt;ParsedRes:0x007f8ec34b35d0
          @description="server URL",
          @key="server",
          @restriction_type="string",
          @title="Server"&gt;,
         #&lt;ParsedRes:0x007f8ec2a64228
          @default_value=
           #&lt;ParsedResRestrictionValue:0x007f8ec3437de0
            @type="bool",
            @value_bool=false&gt;,
          @description="Use Web logon",
          @key="weblogonMode",
          @restriction_type="bool",
          @title="Use Web logon for authentication"&gt;],
       @restriction_type="bundle",
       @title="Configuration"&gt;],
    @restriction_type="Array",
    @title="Configurations"&gt;]&gt;


The ParsedRes can have multiple nested_res that contains and a list of ParsedRes. It can be nested up to two levels deep.

I'm wondering what will be the best design for this kind of data. I would to need to be able to fetch the same tree to display it in the future.

I thought about having a table that can link to a parent and daughter

CREATE TABLE parsed_res (
  id           SERIAL PRIMARY KEY,
  -- other columns
  parent_id    INT,
  daughter_id  INT
);



Is it a good idea?
What is the best way to fetch this tree (subselect?)
ltree ?
Two methods of querying hierarchical data:


Ltree. You can see an example of me using it in a similar question here.. The problem with ltree is you have to serialize the relationship yourself. It's a flat table. This is a pita. Updating it also kind of sucks.
Self-referencing table. You can see an example of me using it in a similar question here.. This is probably ideal because you're only storing the identifier of the parent. This create a simple hierarchical data type that you can query with a RECURSIVE CTE. 


Example table,

CREATE TABLE foo (
  idfoo          serial    PRIMARY KEY
  idparent       int       REFERENCES foo
  description    text,
  key            text,
  restriction    text,
  title          text,
  default        text
);


Then you insert your root, and your children which point to the parent.