1

We have a multi-vendor marketplace (e-Commerce) system and plan to move our data structures into a polyglot architecture to improve the performance of reads (critical) and writes (not as critical).

Offers are placed on a product by several dealers. We have local and online offers and the default offer (first to show in the result) is the cheapest online offer.

Request types

Therefore we typically have two types of requests, as the image shows. One for Lists (Search results) and a Details view with all offers (local and all/online).

Entity Relationships:

  • 1 Offer has 1 Product
  • 1 Product has N Offers
  • 1 Offer has 1 Dealer
  • 1 Dealer has N Offers

My idea is to store the product information in a Cosmos Document Storage since the product information changes rarely. Product entities have search filters (characteristics) as key-value pairs.

Here is a simplified JSON of an offer:

{
 "offerNumber": 1234,
 "dealerId": 1000,
 "price": 19.99,
 "quantity": 5,
 "product" : {
  "title": "My Title",
  "filters": 
  [
   { "key": "value" },
   { "key": "value" }
  ],
  "description": ""
 }
}

An offer typically has a stock (quantity) and price as you see in the model. My first approach was to simply store the cheapest online offer in the document database and all other offers into a relational database (Azure SQL).

Because of frequently changing stock and price information and due to the nature of document databases not being optimized for updates, a relational database seems to be the more appropriate storage for the offers. For the products, since they don't change frequently, a document database is the better choice.

My concern is the volume of updates made to stock and prices. Is there any way to optimize a search index using both sources or does anyone have experience with similar use cases and the different storage types on Azure?

  • 1
    Why not use a search indexer like Lucene, Elastic Search or Solr? That's what they are for. Build an arbitrary search index based on any data source or sources you want. – Greg Burghardt Apr 16 at 0:12
  • @GregBurghardt This is currently my preferred conclusion after researching different approaches. I found this blog (blogit.create.pt/ricardocosta/2017/11/10/…) where a similar use case is described. My only concern is the frequency to update the index. But other approaches come with many more downsides. Thanks for pointing this out, it confirms my thoughts. – Michael Staples Apr 16 at 10:31
0

Standard indexers in Azure Search are not able to support this complex data structure to build an index with the Pull approach. Structure: 1 Product in the documents database n offers in the SQL database. For more simple structures I found this example: https://docs.microsoft.com/en-us/azure/search/tutorial-multiple-data-sources which works fine if the collection is saved as a collection in the source. In terms of SQL, the query result itself returns the collection without being wrapped, which fails to auto-merge using the Pull.

Azure Search supports two types of data ingestions, Push and Pull. Since the Pull approach is not an option due to the issue described, we added an additional Layer (a webjob or function) to deal with data aggregation and pushing it to the index. Following the documentation https://docs.microsoft.com/en-us/azure/search/search-what-is-data-import.

Webjob

Thank you, Microsoft, for the excellent Support going into detail with me to check the possible options.

For more use cases related to retail scenarios here is a good point to start: https://github.com/AzureCosmosDB/scenario-based-labs/blob/master/Retail/HOL%20step-by-step%20-%20Cosmos%20DB%20scenario-based%20labs%20-%20Retail.md

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.