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.