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