this question is related to system design. Suppose I have a car part inventory and I want some of the parts to be replaced, and in that workshop a huge amount of cars come every day.
We assign part on the basis of the car's make-model-variant-year and multiple parts can be compatible with multiple make-model-variant-year.
Also, there can be millions of records of car parts.
In a car we may have to replace 50 parts.
So with RDBMS comparing make-mode-variant-year, part cost and several factors if we assign parts to the car it will take a lot of time.
Solution could be we can use queue to assign cars one by one and transactions like reducing parts from inventory and adding it into out requested parts could be done safely.
But can we optimise this time by using some cache in between (by storing parts list with make-model-variant-year)? one more problem I am assuming there is to manage quantity in cache and RDBMS all the time.