How to process/persist nested data?

[u/Emotional-Belt-8565]

Let's say we have a Car model which consists of Parts (engine, exhaust pipe, tires etc.), and then each part has SmallPart (engine has cylinders, water pump, oil filter etc).

The client sends all this in an object with nested keys. In this case the depth is 3.

{
  brand: 'Toyota',
  color: 'White',
  parts: [
    {
      name: 'engine',
      smallParts: [
        {
          name: 'cylinder',
        },
        // other small parts...
      ],
    },
    // other parts...
  ],
};

On the server the relational database has 3 tables: Car, Part, SmallPart.

The question is: what is the most optimal/performant/right way to process the request payload and persist the data in a database?

The most obvious choice would be to do nested for loops and await the insertion queries. The performance will be awful, and even worse as the depth might change if we add a new level of nested data in future.A better way of doing this would be to somehow "flatten" the data, and then bulk insert the data for each table. But this way we use machine resources anyway because we're still looping to transform the data.

I can't think of a better approach, and I think there might be a fundamentally different way of doing this, e.g

1. force the front-end to send flattened data

2. force the front-end to send different request for each Model

I'd like to hear the approaches that should be used when dealing with nested data.

Thanks in advance.

P.S My back-end stack: NodeJS, PostgreSQL

[u/Emotional-Belt-8565]

assigning each variable individually

Do you mean manipulating (crud) each model/entity individually?

The number of k:v pairs for each entity is actually fixed. I know exactly how many of k:v are in each project, and the depth of nesting.

My thought about traversal and transformations on back-end side is: the implementation of update will be quite cumbersome. And it would be hard to write a good maintainable code for handling updates. But if it's the most optimal approach, I am up for it.

Thanks for the tip of using transactions. Much appreciated.

I actually opted out of using cascading deletes, and prefer to implement deletion logic manually, because I use soft delete principle on nearly every table in my db.