let's say we're building an Ecommerce Marketplace.

We have Sellers and each Seller has Products. We want to display a list of available Products across all Sellers to Buyers.

But, we only need to display products according to some complex logic, e.g. :

  • product must have a enabled field set to true
  • product name must be of length > 2
  • product price must be bigger than 5
  • product's seller must have a enabled field set to true
  • product's seller must have paid a subscription
  • product's seller must have a positive tracking record
  • buyer must be of legal age to buy the item
  • ... and so on.

How would you handle all this logic in the "display a list of available Products across all Sellers"?

Also, we may have future additions/removals of requirements, and this should be considered in how to handle this.

What we considered:

  1. implementing all of these controls in sql

    • works, but can be really complicated and the query might not be that optimized
  2. implementing all of these controls in application code and cache the result

    • works, but how do purge the cache on changes? What about changes in requirements (we add/remove a new requirement). Also adding the cache purging (lets say we purge all products cache on seller subscription edit), leads to a lot of complexity
  3. cache with an expire time, but it's hard to get the time right

    • if we have requirements based on the user, how do we handle its caching? We might have a products which should show up for one user but not for another one
  4. running a scheduled task to check every single product and storing the result somewhere and querying that

    • more or less the problems as 2.
  5. iterate across all products and run the application code checks every single time

    • this is completely wasteful, but there are no problems if requirements change
  6. some of the requirements may be run only on product edit (product name must be of length > 2), run only the ones which may change on query

    • how would you handle the changing in requirement "product name must be of length > 2" to "product name must be of length > 3"? We may display items with 2 letters names, which is against the requirement, but this requirement is only run on edit and the product will not be edited again.

The marketplace is just an example, but the same would apply for many different projects (permissions checks, ecommerce promotion prices, ...)

Is there a software design pattern or a series of pattern to solve this?

  • Yes, you need a cache, or rather a data view. Since this data doesn't change too often you can update the view on data update. If requirements change then you need to recalculate entire view (which can be done incrementally and just accept a window of inconsistency). Complexity is a price you pay for performance. Which is not a reason to be scared.
    – freakish
    Commented May 17 at 7:43
  • Do it in the DB. It "might not be optimized" unless you can create an index or maybe a materialized view for the static parts of the query. E.g. Postgres supports a "partial index" that can be super helpful here. Some NoSQL DBs like OpenSearch (formerly Elasticsearch) can automatically cache results of boolean filters. In practice, just doing a linear scan through all products might actually be performant enough up to a few million SKUs. If you cache query results, use a short TTL, and use a hash of the query (incl parameters) as the cache key.
    – amon
    Commented May 17 at 8:09

2 Answers 2


Group these by the relevant item, into product properties:

  • product must have a enabled field set to true
  • product name must be of length > 2
  • product price must be bigger than 5

Whenever you edit a product, recompute and write a flag "cachedProductIsValidForSale".

  • product's seller must have a enabled field set to true
  • product's seller must have paid a subscription
  • product's seller must have a positive tracking record

Whenever you edit a seller, update "cachedSellerCanSellProducts".

  • buyer must be of legal age to buy the item

This can only be computed knowing the individual buyer, so it's probably best not to cache in the database.

Make sure the cached flags are also indexed, and then it's a simple matter of doing a JOIN to find valid products from valid sellers.

  • Is there any code repository I can take inspiration from? This seems like a common pattern, i can definetly implement everything without problems, but some kind of reference as to how to use this in a generic way would be great!
    – Staskij
    Commented May 17 at 18:42

One can use events. If you change a product validation rule, then fire an event. Then some process picks up the event and says, I need to refresh the product catalog. One needs to be careful how to manage this. For example, let's say users make 1000 product rule changes. We wouldn't want to refresh the catalog 1000 times, maybe just once at the end or when they are done making changes, otherwise it would be wasteful. So, running it on a schedule would be cost effective, or give an admin user the right to run the update when needed, otherwise a bunch of CPU cycles have been wasted for the other 999 updates.

Products are either valid or not. Invalid products don't show on a query unless you're in "admin" mode and you want to see all the products regardless. It should be straight forward to write a query with that criterion.

For product and product seller rules, this would be the way to go as the catalog can be updated when a product rule changes or a product seller rule change.

For user rules, there are some options. One potential option would be to create a product, product_user, and user tables. As user rules are added/changed, the product_user table would be updated adding or removing any records based on user rules. This could also be run on a schedule. One can be smart about this and only update the lists for active users. Can look at last login time and if they have logged in recently update their list. Another option is when a user logs in and their product list is out of date, just pop a message saying the system is updating your product list and will be available shortly.

As you have mentioned, you can do this all in real time by creating dynamic SQL queries, but performance will eventually suffer, and it will be complex to manage. The events balance the complexity by using periodic refreshes on the catalog and user tables. So, one will have to consider those tradeoffs. The refresh option allows for the same queries to be run against the database regardless of having many rules, so there will be consistent performance in the long run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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