The project I am working on has deep parent/child relationships that needs to enforce availability.
Imagine we are a large Worldwide Electronic Seller (Best Buy) and selling mobile phones
We have Regions (Asia,Europe,North America..), Countries within Regions, Warehouses within Countries,Stores which are working with Warehouses.
Here are the 4 levels that are having parent-child relationships.
(Level 1) (Level 2) (Level 3) (Level 4)
Regions -> Countries -> Warehouses -> Stores
We are only selling Mobile Phones, and we have 100 different models available in total. The availibility of individual phone can vary in all these levels (availability points). For example :
By default, all mobile phone models are available for all stores.
We only sell 100 phone models, - Only 80 of them are available in Asia - Out of 80 only 60 of them are available in India - Out of 60 only 50 of them are available in the Delhi Warehouse - and finally out of 50 only 45 of them are available in New Delhi Store 1
Also some of the attributes of the phones might change between these availability points.
For example: Nokia Lumia 830 might be marketed as Lumia X in India. India might offer Vodafone by default for Nokia Lumia 830 but Delhi Warehouse can choose to override this and can offer another simcard provider.
So far I have come up with 2 ways to handle:
1- Applying all available mappings in every level.
Therefore when we make 90 Nokia phones available for Asia region, we will create.
- 90 entries at region Level
- 90 x Number of countries entries in Level 2
- 90 x Number of countries x Number of Warehouses entries in Level 3
- 90 x Number of countries x Number of Warehouses x Number of Stores entries in Level 4
Therefore someone changing the availbility at store level in the Asia region can see all the products available, and filter as they wish or change the attributes. The Delhi Warehouse cannot sell 10 of them, the New Delhi store can not sell 5 of the specific available at the warehouse. Hence
We will remove - 10 mappings from the warehouse - 10 x Number of all Stores mappings that are supplied by the warehuse from above - 5 x mappings from the specific New Delhi store
This approach is easier to handle in the code. But all the operations are creating lots of data records and managing this with transactions could be problematic.
2- Just creating records for the ones are not available in selected level and accept everything is available in every level.
In effect, by default, all products are available globally, unless specific restrictions have been created at the required level
Therefore we make 90 Nokia phones available for Asia region, we will create:
90 entries in Level 1
If someone changes the availbility at store level in the Asia region, they can see all the products available and filter as they wish or change the attributes. Suppose the Delhi Warehouse doesn't stock 10 of them. The New Delhi store doesn't stock 5 of the warehouse available phones. Hence
We will add
10 mappings x Number of Stores to Level 3
- 5 Mappings to the New Delhi store
But implementing this approach and to find the available mobile phones in stores will require reading values from parents.
Also to be able to override an attribute of a phone model in every level in a simple and maintainable way is becoming a challenge.
I have been thinking about how to solve this problem in a nice/clean way and whether using Document Database or Data Warehouse would actually be a better fit.