1

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 ?
0

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.

  • Thanks a lot. I will need to be able to update part of a nested record and display it with hierarchy, ltree doesn't fit, will choose self-referencing table. – Mio Apr 4 '17 at 18:37
  • 1
    Note: you can update rows when you have ltree structure. The updating issue refers when you need to update the actual ltree column, e.g. when you want to move a node from under one parent to another. – ypercubeᵀᴹ Apr 4 '17 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.