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 phonesWe 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      ->       StoresWe 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 1Also 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 290 x Number of countries x  Number of Warehouses entries in Level 390 x Number of countries x  Number of Warehouses x  Number of Stores entries in Level 4Therefore 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. HenceWe 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 storeThis 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 levelTherefore we make 90 Nokia phones available for Asia region, we will create:90 entries in Level 1If 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. HenceWe will add10 mappings x Number of Stores to Level 35 Mappings to the New Delhi storeBut 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.
Hierarchical structure that needs to enforce availability
design patterns;object oriented design;database design;trees;relationships
After going trhough great responses on here . I have implemented (Joel Brown's answer on the link)https://dba.stackexchange.com/questions/28845/modelling-hierarchical-attributeswithout visitation numbers . I went through use cases and that approach is solving each of them also it is generic enough that allows me to override any field i would like to. Sadly i cannot mark Vlad's comment as a answer.  