0

Good day

When starting a new project and you have access to the db as well as writing the code.

In this specific case it is PostgreSQL and Java EE with JPA and Hibernate.

Should one:

  1. Aim to create a normalised as possible DB using lots of key constraints on table?

  2. Create as much of the business logic as possible as views and procedures inside the DB?

  3. Use views inside the DB to display the normalised data as human readable data where the FKs are replaced by the detail they point to?

  4. Attempt to avoid any SQL apart from SELECT via em.find and create via em.persist and merge via em.merge in the Java JPA code?

Benefits I can think of are:

  1. The RDBMS is created to optimise queries. So complex join SQL with CASE THEN should run much faster as database views compared to trying to do the same with JPA using the criteria builder with plenty of pojo classes needed to map the results.

  2. If a complex select view in the DB is mapped as an @Immutable @Entity in the Java model, trivial NamedQueries can select only the row / s needed because the complex view in the DB is seen as a 'simple' table in the Java application right?

  3. There should be a lot less network traffic between the app server and the DB because the complex SQL resides in the DB and the app does not need to do all the joins and case then code, or does Hibernate create a native query similar to the one that would be in the DB and send that over the network? Still writing complex SQL is much easier in SQL as compared to using the criteria builder or JPA or even wrapping the native query in a native query annotation?

Or am I completely missing something fundamental to ORM here that makes the above invalid or even counterintuitive?

Are there any good sources on the above I can read that you recommend please?

  • 1
    Database first design fails the high hanging fruit concepts of unit testing and code reuse. If neither is a concern, it's a quick way to get stuff done. Also, see something like dba.stackexchange.com/questions/76973/… . There's a general consensus in there against complex queries. – RandomUs1r Jul 12 '18 at 22:54
  • 2
    I generally agree with your approach, but I can't give it as an answer to all situations as every one is different. Given my view that all business is applying process to data, I'd certainly get my DB correct first. I also agree that intensive data processing is best handled on the DB (mostly, but not always). Trivial lookups and the odd OLTP type operation I'd use ORM but when it gets bulky I'll drop back to view/SP. So not an answer, because there is no one correct one. This is what architects are for. – LoztInSpace Jul 13 '18 at 1:16
6

In the long run, going database model first will very often pay off compared to what appears to be a quick win of going client model first. Before I explain, let me just disclaim that your domain model is something entirely different and it should be defined even before worrying about the database layer.

Also, there are trivial cases where client model first is a win in the long run as well. I'm assuming you're not going to design a trivial database, but e.g. one with 500 tables.

Thinking in terms of subsystems

The database and its client data access layers are a subsystem of your entire system. Again, your domain model spans your entire system, your database model is only relevant to your database and its clients.

A lot of people mix up these concepts and leak their database models throughout the application, which is often mostly written in a client language like Java. This is why they think that Java is the right choice to modelling things. That's not a sound approach.

The database and its clients should operate on the database model, which is best expressed in the database's own modelling language, which is called DDL. All database client models (e.g. those by ORMs) should be derived from the former, e.g. by using a code generator, which I've illustrated in this blog post here.

Some ORMs (notably Hibernate / JPA, but also jOOQ) allow for generating the database model from a client model representation, which may be convenient at times, e.g. to prototype your database model, or to create a derived test database on a different vendor, etc. This is not the way to go to architect your entire model, it's just a convenient utility!

Significant advantage of going database first

The most important advantage is the fact that from the very beginning, your model will be more sound, more normalised, and using more vendor specific features, such as storage clauses, etc. You will think in terms of your vendor's feature set and you will already think about how to migrate / apply increments to your model from the very beginning.

This means your application will be of the same quality when you develop it as when you go live, as opposed to tediously figuring out how to increment your "legacy" production model once you're live, because you've never thought about this before.

Regarding your specific questions:

  1. Aim to create a normalised as possible DB using lots of key constraints on table?

    Of course! Normalisation is a very good thing and it is always your default choice. You can always denormalise in situations where it makes sense (specific performance issues), but the default is normalisation.

  2. Create as much of the business logic as possible as views and procedures inside the DB?

    This is a matter of taste. The proponents of this approach tweet under the #SmartDB hashtag. I tend to distinguish between data access logic and other business logic. Data access logic should definitely be close to the data. It will lead to better designs and much better performance. I personally don't think (unlike the #SmartDB proponents) that you should shield all database models from the clients and expose data only through procedures. But these are simply opinions. The most important thing is that your data is normalised, and that you use as much SQL (i.e. the set-based language) to access it.

  3. Use views inside the DB to display the normalised data as human readable data where the FKs are replaced by the detail they point to?

    Sure, why not? There are advantages and disadvantages to this approach. I've always liked it.

  4. Attempt to avoid any SQL apart from SELECT via em.find and create via em.persist and merge via em.merge in the Java JPA code?

    Why? While a lot of SQL can be placed in the database in views / procedures, some SQL is dynamic by nature. Of course, you could still implement dynamic SQL inside of stored procedures, but that's going to be a bit of a hassle. SQL builders like jOOQ offer themselves to construct sophisticated SQL statements dynamically, so why not consider that option?

Regarding your stated benefits:

  1. The RDBMS is created to optimise queries. So complex join SQL with CASE THEN should run much faster as database views compared to trying to do the same with JPA using the criteria builder with plenty of pojo classes needed to map the results.

    Of course! Move data access logic into the database using SQL

  2. If a complex select view in the DB is mapped as an @Immutable @Entity in the Java model, trivial NamedQueries can select only the row / s needed because the complex view in the DB is seen as a 'simple' table in the Java application right?

    A complex select view is what JPA folks call a projection and it does not project entities, but tuple streams, or whatever you want to call them. You can use any mapper for that and JPA offers some tools, but beware not to think of your results as entities.

  3. There should be a lot less network traffic between the app server and the DB because the complex SQL resides in the DB and the app does not need to do all the joins and case then code, or does Hibernate create a native query similar to the one that would be in the DB and send that over the network? Still writing complex SQL is much easier in SQL as compared to using the criteria builder or JPA or even wrapping the native query in a native query annotation?

    Yes, absolutely. JPQL or Criteria API allow for a little bit of sophistication to help you fetch entities. But in most cases, SQL is a better choice.

Your final question

Or am I completely missing something fundamental to ORM here that makes the above invalid or even counterintuitive?

I think you're missing on the point that ORMs like JPA are object graph persistence APIs. You use them to load a bunch of related entities (graph), mutate them and their relationship, and persist them again.

The idea here is that in no way you want to write 20 INSERT, UPDATE, DELETE statements in the optimal order to keep the transaction as short as possible, possibly batching things manually, etc. This is where ORMs shine.

Read only access, reporting, ETL, or bulk data transfer? That's not where ORMs shine. Use SQL and project to tuples / arrays / DTOs / data classes / whatever you want to call them.

I've wrapped this up in another blog post here.

  • 1
    Nice answer (in the sense I agree with it :) ). I wish I had taken the time to elaborate my thoughts on this, but you've summed them up nicely. Thank you. – LoztInSpace Jul 13 '18 at 8:31
  • Thank you Lukas for this detailed insightful reply. It is most appreciated. – Letholdrus Jul 13 '18 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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