database design – Best practice or patterns to couple complex custom objects to custom logic

In many project that grow complexer 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 complexer. 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.

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.