I am building an application where I will store some facts corresponding to the product, location and time dimensions. For example, a particular product P1 sold 10 units at a store S1 in a particular month T1. All the dimensions will have levels with a hierarchy among them - for example - Year/Month/Week/Day for time dimension. The members (not sure if members is a right word) of each level will also have hierarchy among them - for example - 2014/Sep/1st Week/3rd Sep and of course this hierarchy matches the hierarchy among the corresponding levels. Similar is the case for other dimensions. Implementing this structure itself is a bit tough going by the options for representing hierarchical data and the choice should be dictated by frequency and volume of data that is to be inserted/updated/deleted versus selected. I can do some research and pick the most optimum solution for my case.
However, the real difficulty I am facing currently is modeling an alternate space where the fact data will live. Referring to the example I cited above, assume that P1 is a member of the product dimension level "Article" in the hierarchy Category/Subcategory/Article and S1 is a member of store dimension level "Store" in the hierarchy Country/City/Store. Now assume the store S1 does not keep the item P1 in the month T1 and we represent this decision using the flag IS_ACTIVE. That is, IS_ACTIVE=N is a fact and its context is {P1,S1,T1}. Also note that IS_ACTIVE is the attribute and N is its value. However this context {P1,S1,T1} itself is an instance of the meta context {Article, Store, Month}. And I need to store this meta context in the application as well. Reason is that there may be a place in the application where I may need to fetch a list of other possible attributes (for example, REBATE_OFFERED_PERCENT) corresponding to the meta context {Article, Store, Month}.
I have figured out a normalized relational schema design for all this but it is too convoluted and in my opinion will not be performant. I am looking for an alternative solution like a NoSQL database which can serve my needs since there is a some hierarchy involved here. Or, is my problem domain more amenable to a relational schema design?
This seems like a standard problem that should appear in multiple domains but I could not find any articles regarding this. Also, is there a branch in abstract mathematics which has a relevance to this problem? Is there a standard terminology to describe such problems? I am willing to read up on some theory before implementing a solution for it.
