3

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

5
  • 7
    What to do when putting logic into the database seems to be the only option? -- Put the logic into the database. There's no hard and fast rule, so long as you understand the tradeoffs, and there are legitimate reasons for doing work directly on the database server. Commented May 6, 2021 at 21:45
  • 3
    In this particular case, it seems to me that the native query is entirely justified. Commented May 6, 2021 at 21:47
  • 2
    Agreeing with @RobertHarvey. But the "how do I test?" question might deserve to be a separate question. And might have already been answered.
    – joshp
    Commented May 7, 2021 at 4:10
  • Using a view/materialized view and a @Subselect could do the trick, and can improve readability (you could see that this code is using a view and you could store and sync the view directly in the code)
    – NeeL
    Commented May 13, 2021 at 20:44
  • 1
    Sending a logic-performing query to the database is not "putting logic in the database" - the logic is still specified in the code, even though it runs on the database server. Commented Sep 15, 2021 at 8:50

2 Answers 2

2

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.

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

8
  • 1
    "Basically, database procedural languages are terrible programming languages to work with." - profound and insightful commentary on some of the most long-lived languages which are still in routine use.
    – Steve
    Commented Aug 15, 2021 at 14:20
  • @Steve yeah, I think we've all seen it. But it's illuminating to consider: while the language is terrible, people prefer system-simplicity overall. All that should be required is better tooling, and a better language, and many people would be very satisfied. (Perhaps transpiling from a "good language" to TSQL, and treating TSQL as a byte-code that you shouldn't look at? Typescript to TSQL?) Commented Aug 16, 2021 at 4:37
  • Whilst almost everyone thinks SQL could be better, I've yet to see a language that makes an appreciable improvement on it. It contains an enormous amount of essential complexity in a very subtle integration. If anything, it remains so advanced that the profession as a whole has not yet fully digested all its concepts and features.
    – Steve
    Commented Aug 16, 2021 at 19:31
  • 1
    The logical fallacy here is the idea that SQL is a general-purpose programming language. It is not. C++ templates are not regarded as a programming language either, despite claims that they, and some variations on SQL like T-SQL, are Turing-complete. SQL was never meant to express the same concepts as general-purpose programming languages; trying to use it that way is an abuse of its capabilities. Commented Sep 20, 2021 at 14:05
  • 1
    @Steve The complaint was not about SQL. It was about the procedural languages built around it, such as PL/SQL. Those are, indeed, really awkward languages to work with, often with really weird syntax peculiarities, weak overall capabilities, and of course terrible error messages when you get something wrong. Commented Sep 21, 2021 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.