-2

We are given a task to create an Web Service API for a mobile application. The problem is we have to use a legacy database that is not normalized and its very tedious to work with. We are accustomed of using Object-relational Mapping for implementing the Repository layer including the database so that its easier and efficient for developing. But this time it became quite unable for to utilize it.

What we found so far to be a solution, is to use native queries and manually mapping the value to POJO or DTO from the queries. But of course, this lead to more maintenance and complexity brought by manually performing queries considering also the database relationships.

Aside from this approach, we would like to know more opinions and ideas on what approaches are possible that is much effective.

Improve this question
4
  • And I guess, refactoring the database or migrating the data to a normalized one is beyond the scope of the project. Am I right? Commented Oct 7, 2020 at 12:37
  • The existing database is quite huge already and it already has a lot of applications that are using it, so I think refactoring is not possible for now. Actually migrating it to a normalized one is another ideal approach but sadly I think this is not possible for now because I think they still want to stick with this legacy database meaning all new updates will still be there, meaning we still need to pull those data into the normalized one. and I think that will add another layer of complexity. Commented Oct 8, 2020 at 2:38
  • "...has a lot of applications that are using it" - so you have a shared database between multiple applications? That's going to give you a lot of pain in more ways than just because the structure of the DB is not good. (For example, your app would have to take changes to the DB by other apps into account). I would create a service around the DB, and have your app (and other apps as well, if possible) talk to the service instead of directly to the DB. Commented Oct 8, 2020 at 8:22
  • Yep we have a shared database between multiple applications. I get your point about creating a service that will act as a middle man for the DB and the applications using it. But I guess that would be beyond the scope of our project at hand. But its a good suggestion, hope they would decide to implement it that way soon. For now we'll just have to figure out whats the best approach for directly interacting with the DB for our application. Commented Oct 8, 2020 at 9:07

1 Answer 1

Reset to default
0

The problem is we have to use a legacy database that is not normalized and it is very tedious to work with. We are accustomed to using Object-relational Mapping for implementing the Repository layer including the database so that it's easier and efficient for developing. But this time it became quite unable to utilize it.

The ideal answer (to me) would be " normalize the database first". But I know that real life often collides with my ideals. Re-design an existing database is likely to be out of the scope of any Web App development. It's possible but it depends on many things, being time and money the main constraints. Knowledge is next on that list and convenience following close.

So, I have to adapt myself to the circumstances and I choose the lesser evil. "I will let the database as is, and I will look for a flexible tool to abstract that mess from the source code".

What we found so far to be a solution, is to use native queries and manually mapping the value to POJO or DTO from the queries. But of course, this leads to more maintenance and complexity brought by manually performing queries considering also the database relationships.

ORMs are not way better than you at building performant queries1, overall if the database is not normalized and you have to make weird relationships by columns of different types and things like that.

You can write as good or better queries than any ORM, and the most important is that you can write the precise query you need for each situation. But you are right, ORMs brings a lot of abstractions and reusable solutions that save us time and headaches.

But, not all is lost. There are other ORMs, no so sophisticated (I'm afraid) but based on convention over configuration as those you are probably used to. These ORMs let you declare native SQL statements (even dynamics) and map results to DTOs. The first that comes to mind is MyBatis which is also integrated with Spring Boot. It's compatible for non-Spring Boot as well. You will find a lot of guides and tutorials to make it work.

If it's integrated with Spring, you get all the advantages of the Transaction Management too.

1: they are good at building and executing a lot of them, over and over

Improve this answer
4
  • The existing database is quite huge already and it already has a lot of applications that are using it, so I think normalizing is not possible for now. But anyways, this answer really shed light for this situation. I think I'll follow the path of using native queries and the ORM that you mentioned to ease the development. Awesome answer! Thanks Commented Oct 8, 2020 at 2:55
  • Btw, could I also use Hibernate? Commented Oct 8, 2020 at 5:14
  • Hard to say. With Hibernate and the likes, you build the object model and then the DB. When the DB exists and it's that messy tho, it could be challenging. It will depend on how wrong is the DB right now. Before to start coding, make a proof of concept. Try to model first your core, then your persistence model and see how hard is to do so with or another framework. For example, start with the most complex entities and relationships. Test one and another fwk performance, setups, etc. Commented Oct 8, 2020 at 6:23
  • Yup, It's kinda messy. You could say that it has a lot of potential for normalization. So yeah I think we'll have to do some trial and error for this. But probably, well play around with native queries and ORM for mapping data automatically. Commented Oct 8, 2020 at 7:06

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.