Sample Header Ad - 728x90

How to handle a constantly changing schemas and tightly coupled entities (ORM hell)?

-1 votes
1 answer
535 views
Essentially my team's ended up in a situation where Object A, B, C, D, and E (greatly simplified \*sigh*) are different Java objects and each is represented by a single table interconnected with other tables in the DB via foreign keys. Each of the foreign keys MUST be present for the one in the previous chain to exist. We have around 13 different submodules in our application, most of which rely on the shared underlying data in the same database. On top of this, new requirements come in almost daily, requiring that we change large parts of the schema (usually existing parts), our underlying objects, and areas of backend as well. A final nail is that we're being asked to add completely new dimensions to existing data that we never could have anticipated. Now business wants us to rework parts of the data model to better match the UIs representation to better aid in reporting functionality. We're using Hibernate as an ORM framework and primarily use Postgres, though we also need to interface with and have started using multiple SQL Server databases as well. RDBMS and the relational model has been around for decades, but I'm not sure how something as rigid as the relational model has been able to survive (and continue to be used), even as businesses grow more and more complex. How can better flexibility be achieved and coupling issues be reduced in RDBMS? Our team has tried to target normalization of 3NF, but I believe we might have over-normalized. I'm not looking for a silver bullet answer, as I fear there are numerous issues (though some hints on proper ORM to RDMS design would be helpful). Having ended up in this ORM/DB hell, my plan to escape would likely be as follows: 1. Keep entities as a database affair. Never pass an entity object between services or to the UI; always deal with DTOs unless the entity is extremely simple or represents a lookup table. By passing DTOs around, we can avoid tightly coupling interoperating services in the backend and frontend with our data layer. 2. Check particular pain points in the data model and think of how we can overcome them. - One example is user access. Essentially, the system works by checking a combination of values that exist on multiple entities against the combinations a user has access to. Having to add new columns to this combination is difficult because it requires a refactor of every query in the entire system (since join conditions now change)--not to mention special exceptions for certain people/management levels. - Maybe a tag system would work here? Idk just my thoughts. I'd like to hear from the community in general. I cannot imagine my team is alone in facing these issues.
Asked by ElderFuthark (99 rep)
Dec 2, 2022, 03:54 AM
Last activity: Dec 2, 2022, 05:18 AM