I've inherited a somewhat complicated data-model in a SQL Database that I'll soon have an opportunity (read: nececssity) to overhaul. The data-model represents a Product catalog. So there's a table of products with lots and lots of other tables 1-3 generations deep containing category data, localized data, status etc. And there is an API above this data model that allows other systems to query it.
The crux of the issue is that there are numerous complex "Business Rules" in place that put define global filters what data the API returns. (e.g. exclude products from Manufacturer M in Category X, if it's after the 10th of the month, and the stock in Retailer Y is showing <= Z)
These rules were naively implemented in the database in a view.
API -> EntityFramework Query -> View (Containing hacked in Where Clauses) -> Underlying Tables
The issues are obvious (maintenance, lack of testability, performance etc...)
This feels like a business rules problem where based some set of circumstances/conditions, we need to apply some set of global filters.
One thing I've been looking at is the Specification Pattern, where I could, based on some criteria (rules), inject a set of specifications that define these query filters.
This has the benefit of bringing both the rules & the filters up into code. It would also support a more plugin-based archiecture where Specification could be injected at runtime so we wouldn't necessarily have to go through a deployment cycle to add new rules.
Just wondering if there's something else I should consider, or if there are patterns/approaches or existing off that shelf software I should also have a look at.