I would like to kindly ask for an advice about how should one go when designing a databases/collections around such a scenario:
Let’s suppose we are working with 3 data collections:
PRODUCTS, CATEGORIES and POLICIES
PRODUCTS belong to CATEGORIES. CATEGORIES have POLICIES attached that govern how PRODUCTS are handled.
An example use case:
CATEGORY that the product is assigned to: Dairy products
POLICIES assigned: 1. Storage policy 2. Handling and shipping policy
The goal is to be able to attach POLICIES to CATEGORIES and then attach PRODUCTS to the said CATEGORIES so that they would have access to POLICIES and would be handled accordingly.
Now, one of the immediately available solutions online would be to create a reference to the CATEGORY in the PRODUCT document and then create another reference in the CATEGORY document pointing to specfic POLICIES assigned.
However, I cannot seem to find a solution to the following issues:
If an existing POLICY that is referenced inside CATEGORY is modified, how could one make the changes permeate already existing PRODUCTS attached to the CATEGORY and thus inheriting the existing attached POLICIES?
And what if we have thousands or millions of PRODUCTs belonging to a given CATEGORY? What would be a rational way of dealing with the problem 1?
Thanks for the help!