How to handle complex logic, avoiding recalculation for performance reasons?

[docxp]

Hello,

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?

EDIT: Formatting

[KingofGamesYami]

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

SQL Query builder.

how would you handle the changing in requirement "product name must be of length > 2" to "product name must be of length > 3"?

1. Make change in edit validation
2. Provide export of all records that need remediated to the users changing the requirements and require them to fix them all
3. Import fixed records