Polymorphic association in PostgreSQL with JPA/Hibernate

user_4057038 0
Viewed 360

I have three entities video_guide text_guide and discussion that represent type of learning material available on the platform. A student can create topic-wise boards and add learning material to them. And later view material saved to boards. I am finding it a bit of challenge to aggregate data from three different entities into Board entity, keeping the performance and code quality in check. I have come up with three approaches to implement this use case and would like to have your input on the same. We are using PostgreSQL12 with Hibernate.

Approach 1: JPA Inheritance Joined strategy. This should work but it has following downside

  • Known performance issues with Inheritance mappings.
  • Queries become more complex hence overall complexity of the code.
  • Inheritance is not a SQL native concept but a JPA feature.

Therefore, do not see this as a good option to explore futher.

Approach 2: Individual entity mapping

@Entity
public class Board{

  @Id
  private long id;

  @ManyToMany
  private Set<VideoGuide> = new HashSet<>();

  @ManyToMany
  private Set<TextGuide> = new HashSet<>();

  @ManyToMany
  private Set<Discussion> = new HashSet<>();

  ------------
  ------------
}

This will have

  • Simpler code and queries.
  • Better performance on reads(DTO projections).

has following downsides

  • If new type of materials are introduced in the future more associations need to be added.
  • This same use case is required for other features e.g Topic page that displays all the material related to that topic. That means more duplication, more places for code change.

Approach 3: Preview table

Create a new entity material_preview which will hold previews for all material available on the platform.

@Entity
public class MaterialPreview{

  @Id
  private long materialId;
  private String title;

  ---------------
  ---------------
}

Map MaterialPreview to Board entity

@Entity
public class Board{

  @Id
  private long id;

  @ManyToMany
  private Set<MaterialPreview> = new HashSet<>();

  ------------
  ------------
}

Every time a new material is added to the platform its preview is added to MaterialPreview. And this MaterialPreview entity will be used for mapping a material instance to other entities rather than video_guide text_guide and discussion entities.

This will

  • Allow single mapping for all the material types. So, in future if new material types are added no changes to mapping will be required.
  • No apparent issues with the reads performance.

has following downside

  • Additional insert into MaterialPreview.
  • Data redundancy. But I think this should be acceptable since the number of attributes in MaterialPreview will be somewhere between 8 and 10.
  • Attributes not common to all material types will be null.
  • Keeping MaterialPreview in sync with actual material tables. So, if somebody updates title of a video guide we need to update the same in preview table as well.

What do you think would work out best or some other approach I should consider? Suggestions are much appreciated.

Thanks !!

jpa postgresql hibernate database java
1 Answers

It depends whether different learning materials are to be treated in a polymorphic way or not. E.g. a JPA query to get all boards without learning materials is simpler with inheritance than without it:

SELECT b from Board b WHERE b.learningMaterials IS EMPTY

vs

SELECT b from Board b WHERE b.videoGuides IS EMPTY AND b.textGuides IS EMPTY AND b.discussions IS EMPTY

Furthermore, you'd need to change the second query whenever you add a new learning material type (what you basically also mentioned in the approach 2).

Regarding the inheritance strategy, true, Joined strategy can result in complex queries that are generated under the hood, that's why Single Table is used most often in practice (null values in columns belonging to other entity types in a row for an entity never bothered me much).

On the other hand, don't misuse inheritance just to decrease the number of lines of code. If different learning materials really do not logically belong to the same hierarchy and will not be handled in a polymorphic way, then use composition (the famous OOP rule: Prefer composition over inheritance.), as it is more flexible and explicit. In order to reduce repetition, you could utilize @Embeddable classes:

@Embeddable
public class LearningMaterials {

  @ManyToMany
  private Set<VideoGuide> videoGuides = new HashSet<>();

  @ManyToMany
  private Set<TextGuide> textGuides= new HashSet<>();

  @ManyToMany
  private Set<Discussion> discussions = new HashSet<>();
}

Then embed LearningMaterials wherever it is needed:

@Entity
public class Board {

  @Id
  private long id;

  @Embedded
  private LearningMaterials learningMaterials;
}

@Entity
public class Topic {

  @Id
  private long id;

  @Embedded
  private LearningMaterials learningMaterials;
}

Finally, regarding the approach 3, with Single Table inheritance you get it out of the box, and if you still want to read only a subset (preview) number of columns/fields in some use cases, you can map the MaterialPreview (preferably @Immutable) entity to the same table (it is allowed to map multiple entities to the same table), so that you don't need to maintain the redundancy.

user_47547900
Related