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:
- 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.