What to do when putting logic into the database seems to be the only option?

Question

I am currently working on an application that models a complex business process that consists of many steps, each having a 1:n relationship with subsequent steps. The software is written Java using Hibernate and PostgreSQL.

Users request quotes (each containing multiple items). Requests are then matched with suppliers. Both parties negotiate offers, which are versioned. If a buyer likes one or a combination of offers he can go into one or multiple contracts. The process continues in a similar fashion with production runs and the actual delivery, each consisting of another four steps. At any point in the process one step can fail and terminate the branch of the process.

The business requirement is that for different stake holders there have to be searchable and sortable tables for most entities (e.g. requests for quotes, deliveries). Each table row has one little colored box that describes its current "state" (e.g. a request for quote could have a green box with the word fulfilled). The logic that decides which colored box is shown requires to know information about subsequent steps (e.g. a request for quotes is fulfilled when it has only fulfilled contracts and no valid offers).

The technical challenge is performance, code readability and testability.

The initial version used Hibernates @Formula annotation and HQL queries to calculate these state values on each select query. With too much data this became too slow.

The second version used HQL queries to update a database field for each dependend entity after inserting or updating an entity. With more and more complex logic this became too slow and HQL was limiting the readability of queries.

The current version uses Hibernate's native query feature to update each dependent entity's state field after inserting or updating an entity. This approach is performant, relatively readable although it could be better, but not very testable.

My questions:

• Is there some obvious way in which my approach can be improved or in which the logic can be moved out of the database?
• How do I make these UPDATE queries testable? Enormous amounts of test data?

Disclaimer: The actual problem is a bit more complex than the problem described here, as there are often multiple "state" columns per database table (e.g. currently stocked quantity, remaining contract quantity) and there is some master data outside the sequential process steps that also requires state fields (e.g. how many units of an item group is currently in stock).

Answer 1

So essentially you've run into performance issues, and decided to go lower down in abstraction levels. This is a sensible approach although sometimes it's not the most long term approach; but it certainly tackles the problem.

I'd like to respond to something about your concerns and beliefs this is bad:

The current version uses Hibernate's native query feature to update each dependent entity's state field after inserting or updating an entity. This approach is performant, relatively readable although it could be better, but not very testable. (Emphasis mine)

We have 50% or so of our codebase that is SQL queries. We test them. We have setup a test db environment, we mock the data, we run queries and test expected results. This is not the most practical, but it certainly isn't the worst test environment to work with.

If this is you only concern, it is solvable, so if your business requirements push you to use native queries, there is no problem in going in with the flow. May you realize you could use a different database architecture to solve the problem, you'd still have a working solution that could make people bear waiting for an important refactor.

Answer 2

What to do? Organise it well.

In my experience, databases have been terrible due to:

• Database Procedural Languages (TSQL, plsql)
• Internalising (Triggers)

Basically, database procedural languages are terrible programming languages to work with. Conditional logic isn't great, and they only run "on" the database.

The solution is to find a way to "externalise". PostgreSQL has got Notify/Listen capability, that is a great way to externalise:

• Only use Triggers to NOTIFY (and Shadow-table audit if you need)
• Have a background process that LISTEN to a notification
• The background process then simply runs a VIEW to see if there's atomic work to be done.

With that basic mechanism you can build a system of:

• Tables
• Processes

It therefore becomes beneficial to normalise your tables to segment notifications channels further. You mention having many state fields in a table, and that's probably fine, consider them as "summary state" fields. You should drive primary-data from event-capture tables. For example, a "Quote" table might have "Quote Accepted" field as a summary of current state, but you will have other tables "CustomerQuoteRequest", "SupplierQuoteProposal", "CustomerQuoteProposalAcceptance", etc.. that you derive that state from.

Answering your direct questions:

Is there some obvious way in which my approach can be improved or in which the logic can be moved out of the database?

As above.

How do I make these UPDATE queries testable? Enormous amounts of test data?

With the above, each "process" is independent. There is "input data" that should lead to particular "output properties" - that is the output data is not exact, but perhaps a record needs to exist, or one of multiple columns needs to be a particular value.

In my experience, you should start with "end to end" tests first. Input data, then measure the final output beyond multiple processes. Then into the future, you can create individual process-unit tests.

---

The benefit of using "Tables and Processes" is that you can have unlimited domain complexity, and you are not modelling that same complexity in your software, you're only processing it.