Postgresql – Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB

database-designhierarchypostgresqlpostgresql-9.3

I am very new to database design. I am hoping for suggestion or solutions for a feature I am working.

Note: We are majorly using JSONB type in Postgres

Feature:
I have a orders table which would contain the every order information, every order has a unique items user ordered.

Sample Order Schema

{
    id: "parent_order_id_uuid",
    "items": [
        {
            item_id: "uuid-#1",
            details: {
                etc....
            }
        },
        {
            item_id: "uuid-#2",
            details: {
                etc....
            }
        }
    ]
}

I am working on a feature were users can initiate a replacement for the item he purchased for n-number of times.

For Eg – "uuid-#1" can be replaced separately and "uuid-#2" can be replaced separately or both "uuid-#1" & "uuid-#2" n-number of times.

so every time user replace an item I need to create a new order which would only contain the replaced item only.

Sample Replacement Order:

{
    id: "order_id_uuid",
    "items": [
        {
            item_id: "uuid-#1",
            details: {
                etc....
            }
        }
    ]
}

{
    id: "order_id_uuid",
    "items": [
        {
            item_id: "uuid-#2",
            details: {
                etc....
            }
        }
    ]
}

Requirements:

At any point in time, i need to able to create a tree-like structure based on the "parent_order_id_uuid".
At any point in time, I need to able to query the replaced order based on the "parent_order_id_uuid".
I need to have relationship between parent_order_id and replacement_orderIds + parent_item_id and replacement_ids + replacement_ids and replacement_ids

How to store this data in Postgres to get the all this requirement, Once again, I am okay with storing this data as JSONB. Some design suggestions will be helpful for me.

(Parent to Child + Child to Parent + Child to Child)

Simply to Put my requirements:

Parent to Child for N-Times

{
  "parent_order_id_uuid": [
    {
      "replacement_order_id_1": {
        "items": [
          {
            "parent_item_id": "uuid-#1",
            "item_id": "uuid-#3"
            "details": {
                etc...
            }
          }
        ]
      },
      "replacement_order_id_2": {
        "items": [
          {
            "parent_item_id": "uuid-#2",
            "item_id": "uuid-#4"
            "details": {
                etc...
            }
          }
        ]
      }
    }
  ]
}

If this is not very verbose, please let me know.

P.S: This is my first question in Database Stack.

Thanks in Advance for helping minds.

Best Answer

First, as always a_horse_with_no_name is right. Read this

As to how you can factor out your jsonb. You can use a single-table hierarchy.

CREATE TABLE items (
  item_id    serial  PRIMARY KEY
  parent_id  int     REFERENCES items,
  details    jsonb
);

At the very least with this scheme you've removed out the IDS. Now you just use a recursive query to query the data.

For more information look up

Related Solutions

Sql-server – Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item

In my experience, trying to encode multiple data points into a single column always ends up being more trouble than it's worth. Sure, it seems cool and clever to use BITWISE operators, but there are many things that go wrong and it won't always be efficient to test those bits without cumbersome and unintuitive workarounds. It's the same reason we stay away from storing comma-separated lists, JSON strings etc. in a single column - eventually you care about viewing or filtering on those distinct bits which you now have to extract, sometimes expensively.

With the information I have, my vote is for three separate BIT columns. They will still collapse to similar storage patterns as a single column with the three bits on/off, and can be made more efficient individually and across the board in several ways, including:

data compression
sparse columns
filtered indexes (e.g. WHERE allow_returns = 1)

Someone else advocated for three CHAR(1) columns. These do not benefit from storage collapse and also require a check constraint, making them less than ideal in my mind.

Now, my answer might change if you say, "well, what if I might add 15 other attributes in the future?" I certainly don't think it's wise to build the columns this way if they're not relatively static - changing the schema (and therefore all of the code and interfaces to it) for every new or changed attribute is going to be a royal pain. So in that case you might want to consider EAV - where the attributes are not part of the metadata but part of the data. There are a lot of objections to EAV, mostly around performance and the difficulty in enforcing constraints (in this case unlikely to be an issue if all of these attributes are either on or off), but it worked quite well for us at my previous job. You might model it like this:

CREATE TABLE dbo.Attributes
(
  AttributeID TINYINT PRIMARY KEY,
  Name VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.ItemAttributes
(
  ItemID INT NOT NULL 
    FOREIGN KEY REFERENCES dbo.Items(ItemID),
  AttributeID TINYINT NOT NULL 
    FOREIGN KEY REFERENCES dbo.Attributes(AttributeID),
  Status BIT NOT NULL,
  PRIMARY KEY(ItemID, AttributeID)
);

And again, you can have filtered indexes here to make certain queries much more efficient, such as (imagine the AttributeID for "allow returns" is 10):

CREATE INDEX optAllowReturns ON dbo.ItemAttributes(ItemID)
  WHERE AttributeID = 10 AND Status = 1;

If you have certain attributes that are not on/off (for example, three states of manufacture or shipping), you can change the Status column to:

Value TINYINT NOT NULL

This can double as an on/off value for attributes that are represented that way, and as tri- or more-state value for attributes that require more than simple on/off. You can also reflect which type is which in the metadata of the dbo.Attributes table.

Postgresql – For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

This does the job:

CREATE MATERIALIZED VIEW speedy_materialized_view AS
WITH RECURSIVE tree AS (
   SELECT id, parent_id, ARRAY[id] AS path
   FROM   gear_category
   WHERE  parent_id IS NULL

   UNION ALL 
   SELECT c.id, c.parent_id, path || c.id
   FROM   tree t
   JOIN   gear_category c ON c.parent_id = t.id
   )
, tree_ct AS (
   SELECT t.id, t.path, COALESCE(i.item_ct, 0) AS item_ct
   FROM   tree t
   LEFT   JOIN  (
      SELECT category_id AS id, count(*) AS item_ct
      FROM   gear_item
      GROUP  BY 1
      ) i USING (id)
   )
SELECT t.id
     , t.item_ct       AS count_direct_child_items
     , sum(t1.item_ct) AS count_recursive_child_items 
FROM   tree_ct t
LEFT   JOIN tree_ct t1 ON t1.path[1:array_upper(t.path, 1)] = t.path
GROUP  BY t.id, t.item_ct;

count_recursive_child_items is counted separately for each category, so I am not convinced it's the fastest possible way for deep trees.

However, aggregate functions are not allowed in recursive CTEs.

Recursive Function

Strictly speaking, it's really iterative - but so is a "recursive" CTE.

You could build a function working with temp tables. You need to know your way around plpgsql or there is too much to explain.

CREATE OR REPLACE FUNCTION f_tree_ct()
  RETURNS TABLE (id int, count_direct_child_items int, count_recursive_child_items int) AS
$func$
DECLARE
   _lvl int;
BEGIN
   -- basic table with added path and count
   CREATE TEMP TABLE t1 AS
   WITH RECURSIVE tree AS (
      SELECT c.id, c.parent_id, '{}'::int[] AS path, 0 AS lvl
      FROM   gear_category c
      WHERE  c.parent_id IS NULL

      UNION ALL 
      SELECT c.id, c.parent_id, path || c.parent_id, lvl + 1
      FROM   tree t
      JOIN   gear_category c ON c.parent_id = t.id
      )
   , tree_ct AS (
      SELECT t.id, t.parent_id, t.path, t.lvl, COALESCE(i.item_ct, 0) AS item_ct
      FROM   tree t
      LEFT   JOIN  (
         SELECT i.category_id AS id, count(*)::int AS item_ct
         FROM   gear_item i
         GROUP  BY 1
         ) i USING (id)
      )
   TABLE tree_ct;

   -- CREATE INDEX ON t1 (lvl); -- only for very deep trees

   SELECT INTO _lvl max(lvl) FROM t1;  -- identify max lvl to start bottom up

   -- recursively aggregate each level in 2nd temp table
   CREATE TEMP TABLE t2 AS
   SELECT t1.id, t1.parent_id, t1.lvl
        , t1.item_ct
        , t1.item_ct AS sum_ct 
   FROM   t1
   WHERE  t1.lvl = _lvl;

   IF _lvl > 0 THEN
      FOR i IN REVERSE _lvl .. 1 LOOP
         INSERT INTO t2
         SELECT t1.id, t1.parent_id, t1.lvl, t1.item_ct
              , CASE WHEN t2.sum_ct IS NULL THEN t1.item_ct ELSE t1.item_ct + t2.sum_ct END
         FROM   t1
         LEFT   JOIN (
            SELECT t2.parent_id AS id, sum(t2.sum_ct) AS sum_ct
            FROM   t2
            WHERE  t2.lvl = i
            GROUP  BY 1
            ) t2 USING (id)
         WHERE  t1.lvl = i - 1;
      END LOOP;
   END IF;

   RETURN QUERY  -- only requested columns, unsorted
   SELECT t2.id, t2.item_ct, t2.sum_ct FROM t2;

   DROP TABLE t1, t2; -- to allow repeated execution in one transaction
   RETURN;
END
$func$  LANGUAGE plpgsql;

This cannot be included in a CREATE MATERIALIZED VIEW statement because of the use of temporary tables. You could just create another (temp) table with it, acting as a manually maintained "materialized view":

CREATE TABLE speedy_materialized_view AS
SELECT * FROM f_tree_ct();

Alternatively you could TRUNCATE speedy_materialized_view in the function and write to it directly. The function would RETURNS void instead or you could return some meta-information like a row count ...

SQL Fiddle.

Aside:
Column aliases in the recursive term of a CTE are just for documentation since output column names are determined by the non-recursive term only.

Best Answer

Related Solutions

Sql-server – Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item

Postgresql – For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

Recursive Function

Related Question