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

Question

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:

1. At any point in time, i need to able to create a tree-like structure based on the "parent_order_id_uuid".
2. At any point in time, I need to able to query the replaced order based on the "parent_order_id_uuid".
3. 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.

Answer 1

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

• an example with id/parentid/query
• [hierarchy] + [postgresql]