I received an interesting inquiry today. Someone wrote to me asking how to best handle the scenario where every store has its own price on a per item basis. And the number of stores is 400 and growing.
The first way I could think of to do this that would not be obnoxiously unmaintainable is as follows:
- Setup a new table that is independently created (and maintained) – consider making it a profile definition if you want to maintain the data using the Customer and Order Manager
- Store the unique identifier of the product, the store, and the per store price in that table
- Query it using the SetJoin() API when querying the catalog
- Some custom pipeline lookup logic will need to be written as well
The second approach would be to use Virtual Catalogs, where each store has its own virtual catalog. This is easily done provided that the number of base catalogs is small. When issues are encountered is when you are looking to have too many base catalogs in a virtual catalog. Of course, materialization will always help performance as well.
Hope this helps!