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',
},
],
},
],
};
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
force the front-end to send flattened data
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
What I have tried:
I've tried traversing the data on the back-end and doing bulkInsert for each model/entity.