Postgres Design for Nested Resources Linking to Same Type

database-designdesign-patternhierarchypostgresql

I have this kind of data that I need to store in postgres (ruby object). 

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

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
);
  1. Is it a good idea?
  2. What is the best way to fetch this tree (subselect?)
  3. ltree ?

Best Answer

Two methods of querying hierarchical data:

  1. 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.
  2. 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.

Related Question