0

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.

2

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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