Best practice or patterns to couple complex custom objects to custom logic

Question

In many project that grow more complex at a certain moment business rule editors, workflow editors, custom taxonomies and such enter the arena.

In all of these a coupling is made between a complex object e.g. "department" and a business rules e.g. "if department = a or b then allow no access"

In the older times these were hardcoded linked to sql tables and columns, so that in the database itself you would find table X, column Y, reference Z, or more complex. With many customer with older systems this is still the case (often in large monolitic database systems). And very often i find a column with complete lines of SQL for the rules themselves. For example:

Table "CoupleTable"

• Column: DatabaseTable (varchar)
• Column: DatabaseField (varchar)
• Column: SpecificSqlQuery (varchar)
• Column: FK_To_Some_Variable (varchar)

This creates in the database layer hardcoded couplings to database specific concepts and putting this in a UI almost always means CRUD operations by selecting database tables and fields / or / synonyms that in the end are database tables and columns. At many places this originates from an initial data(base) driven design and thinking cap. It also requires in the UI to enter the SQL query itself to then store in the table. Where you should consider that a certain product is installed at hundreds of different places and in all these places these tables contain different sql queries.

Migrating this to a document database/json solves this partly but the queries specifically and often consisting out of the wildest joins and unions are then still a problem as well as introducing new variables with certain logic. Trying to convert these hundreds of sql queries to a higher layer or "something simple" is almost impossible since they are custom with each installation. You would have to invent a new query language that encompasses everything in sql itself.

Secondly these "types" are often not defined hardcoded in a table so there is not a table "department" but department itself is a defined as a function as "combination of employees that have column Z filled with AB" or something alike (but sometimes they are hardcoded tables and fields) (and since this is also a rule/function there is no difference between definining such a group and another business rule)

So in general I wonder what the best practice is to make these layers more independent. In such a way that e.g. a 3rd party rule editor could be downloaded to manage e.g. XACML definitions or generic workflows descriptions or definitions of taxonomies of custom types.

Because even when introducing middle layers these will often be intersection tables with somewhere a linkage to a hardcoded table and field.

So e.g. if a rule was "if employee of role Z is part of Department C or D OR is manager then do not allow access". I would want to store this without storing the table name and field of employee Id's or department ID's in some junction table.

Answer 1

If you want to enable users (not programmers) to write business rules, you will most likely need to implement some business rule definition language that works on the business domain level, not at the SQL level.

This language might be textual, maybe resemble some language the users are somewhat familiar with, or it might consist of structural parts that are manipulated with a visual editor or web forms.

Rules, conditions, and actions would need to be translated into whatever mechanisms are available within the application. The easiest solution would probably be to write some rule interpreter, but it might be possible to translate parts (such as conditions) into SQL or the native language of the application, if it allows for dynamically loadable extensions. This is the place where translation from business domain concepts such as "department" into database tables, columns and joins happens. It might be supported by having additional views that translate from the internal representation within the database to one that is more suitable to the business rule world.

How to store and manage the rulesets depends on additional requirements regarding safety, auditability, version management etc. Database tables often fit in well with the application, but version management might be difficult with them. On the other end of the spectrum would be a textual representation of business rules stored in a normal source code management system. Such textual rules would then be translated into the structure suitable for interpretation within the application and deployed to the application.

For further research, you might lookup the literature on domain specific languages for business processes, I think that should yield some more ideas and solution concepts.