Apply database-level business rules

Question

I am working on a project in which we will need to define certain types of statuses for a large number of people stored in a database. The business rules for defining these statuses are quite complex and subject to change.

For instance,

if a person is part of group X and (if they have attribute O) has either attribute P or attribute Q, or (if they don't have attribute O) has attribute P but not Q, and don't have attribute R, and aren't part of group Y (unless they also are part of group Z), then status A is true. 

Multiply dozens of statuses and possibly hundreds of groups and attributes. People, groups, and attributes are in the database.

Although this will be consumed by the Java application, we also want to be able to run reports directly with the database, so it would be better if the set of calculated statuses were available at the data level.

Thus, our current design plan should have a table or view consisting of a set of Boolean flags (hasStatusA? HasStatusB? HasStatusC?) For each person. Thus, if I want to ask everyone who has C status, I don’t need to know all the rules for calculating C state; I just check the flag.

(Note that in real life, flags will have more meaningful names: isEligibleForReview ?, isPastDueForReview ?, etc.).

So, a) this is a reasonable approach, and b) if so, what is the best way to compute these flags?

Some parameters that we consider for calculating flags:

• Make the flag set a representation and calculate the flag values ​​from the underlying data in real time using SQL or PL-SQL (this is Oracle DB). Thus, the values ​​are always accurate, but performance may suffer, and the rules must be supported by the developer.

• Make a set of flags consisting of static data and use the mechanism of some types of rules to update these flags as the underlying data changes. Thus, rules can be supported more easily, but flags may be inaccurate at a given time. (If we go with this approach, is there a rule mechanism that can easily manipulate data in a database this way?)

Answer 1

In this case, I propose applying the question of Ward Cunningham - ask yourself: "What is the simplest thing that could work?"

In this case, the simplest thing may be to come up with a view that considers the data as it exists, and makes calculations and calculations to create all the fields that interest you. Now download your database and try it. Is it fast enough? If so, well, you did the simplest thing, and everything worked out perfectly. If it is NOT fast enough, good - the first attempt did not work, but you got the rules displayed in the view code. Now you can continue the next iteration of the “simplest thing” - perhaps you write a background task that monitors inserts and updates, and then jumps to recalculate the flags. If it works, fine and dandy. If not, proceed to the next iteration ... etc.

Share and enjoy.

Answer 2

I would advise you not to create statuses as column names, but to use a state identifier and value. such as a client status table with identifier and value columns.

I would have two methods for updating statuses. One stored procedure that either has all the logic or calls separate stored procedures to determine each state. you can do all this dynamics by having a function for each state estimate, and one stored proc could then call each function. The second way would be to have all stored proc (s) that update user information, invoke the stored proc process to update all user statuses based on current data. These two methods will allow you to have both real-time updates for data that has been changed, and if you add a new status, you can call the method to update all statuses using the new logic.

We hope you have one update point for user data, for example, updating a user update, and you can put a saved status update request into this procedure. It will also save you from having to schedule a task every n seconds to update statuses.

Answer 3

The option that I would consider will be for each flag that will be supported by a deterministic function that returns the current value, taking into account the relevant data.

A function may not work well enough, however, if you call it for several lines at a time (for example, for reports). So, if you use Oracle 11g, you can solve this problem by adding virtual columns (search by "virtual column") to the corresponding tables based on the function. Function The result of the cache should also improve the performance of the function.