I am trying to figure out the data model for a retailer.
The retailer has several stores across the country and they are modeled using the following hierarchy:
Channel -> Zone -> City -> Store
Each store contains several articles. And each article has attributes like
- Activation flag (this indicates the presence of article)
- Price
- Supplier
- Warehouse
Now, the retailer can set these attributes at any level in the hierarchy. Consider the following cases:
- Setting price for an article at channel level will apply it to all stores.
- The price set at a higher level can be overridden at any other level. For e.g., at a city level for just the stores in a city or for a particular store.
- This applies to all attributes listed above.
As of now, they have modeled it using RDBMS by defining global rules at the top of hierarchy and calling out exceptions separately as individual rows. Say, price table, will have price set for an article at Channel level and any changes at any level will be specified separately. Obviously, this is not very efficient when fetching the attributes at the store level.
Sample Data
Assume Channel, Zone, City and Store are collectively called entities. Channel will have ids ranging >= 4000, Zone >= 3000, City >= 2000 and stores range from 1 to 1000.
A subset of the hierarchy relationship data is given below:
Channel | Zone | City | Store |
----------+----------+------------------
4001 | 3001 | 2001 | 13 |
4001 | 3001 | 2001 | 14 |
4001 | 3001 | 2002 | 15 |
4001 | 3002 | 2003 | 16 |
4001 | 3003 | 2006 | 74 |
Price Table
ArticleID | EntityID | Price
----------+----------+----------
12345 | 4001 | 2.5
12345 | 2003 | 2.9
12345 | 74 | 3.0
Here, the price 2.5 for channel 4001 will be set for the article in all stores. The next two rows set the price exceptions in some stores. The second price 2.9 set for city 2003 will be applicable only for the article in store 16 as per the hierarchy relationship mentioned above. And the third row directly sets price 3.0 for the article in store 74.
Hope this gives an idea of current model. So, can you please suggest a better way to store this?