Hibernate, Spring Data: Map for storing a key-value OneToMany vs a separate entity

Question

Suppose I have an entity Dog, and an entity Person. A Dog assigns a 1-10 points of loyalty to each Person it knows.

Which approach is better to use:

Store the person-loyalty data in a Map:

@Entity(name = "dogs")
public class Dog  {
  ...
  @OneToMany(cascade = CascadeType.ALL)
  @JoinTable(name = "person_loyalty_mapping", 
      joinColumns = {@JoinColumn(name = "dog_id", referencedColumnName = "id")})
  @MapKeyJoinColumn(name = "person_id")
  private Map<Person, Integer> loyalties;
}

Or create a new entity:

@Entity(name = "person_loyalty")
public class PersonLoyalty  {
  ...
  @OneToOne
  @JoinColumn(name = "person_id", referencedColumnName = "id")
  private Person person;
  @Column
  private Integer loyalty;
}

@Entity(name = "dogs")
public class Dog  {
  ...
  @OneToMany(cascade = CascadeType.ALL)
  private Set<PersonLoyalty> loyalties;
}

Which approach is more efficient and more often used (assuming there would be at most 100 key-value pairs in the map, so that the cost of accessing a value by key should be negligible)?

EDIT:

Dog.java

@Entity(name = "dogs")
public class Dog  {
    @Id
    @SequenceGenerator(name = "dog_sequence",
        sequenceName = "dog_sequence",
        allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
        generator = "dog_sequence")
    private Long id;

    @Column(unique = true)
    private String name;

    @ElementCollection
    @CollectionTable(name = "dog_person_loyalties", 
            joinColumns = @JoinColumn(name = "dog_id"))
    @MapKeyJoinColumn(name = "person_id")
    @Column(name = "loyalty")
    private Map<Person, Integer> loyalties;
...

Results in tables: dogs, dog_person_loyalties:

The second approach:

Cat.java:

@Entity(name = "cats")
public class Cat {
    @Id
    @SequenceGenerator(name = "cat_sequence",
        sequenceName = "cat_sequence",
        allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
        generator = "cat_sequence")
    private Long id;
    
    @Column
    private String name;

    @OneToMany(mappedBy="cat")
    @MapKeyJoinColumn(name="person_id")
    private Map<Person, CatPersonLoyalty> loyalty;
...

CatPersonLoyalty.java:

@Entity
public class CatPersonLoyalty  {
    @Id
    @SequenceGenerator(name = "cat_person_sequence",
        sequenceName = "cat_person_sequence",
        allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
        generator = "cat_person_sequence")
    private Long id;

    @ManyToOne
    @JoinColumn(name="cat_id")
    private Cat cat;

    @ManyToOne
    @JoinColumn(name="person_id")
    private Person person;

    private int loyalty;
...

results in tables: cats, cat_person_loyalty:

So the only difference I see is that cat_person_loyalty has an id (due to it corresponding to an Entity). While the dog_person_loyalties has a composite primary key: (dog_id, person_id).

However, I still don't see why the second approach would be preferable to the first one. Why would I need to know an ID of a person-loyalty pair? How would going with the first approach could potentially adversely affect performance?

Answer 1

Mapped relationships have an entity type on each end. When mapped on one side as a Map, the entities on the other side are the values of that map, not its keys. That rules out your first alternative.

Note also that in order for each dog to assign a loyalty score to each person it knows, you need either a many-to-many relationship or you need the persons to be partitioned among the dogs, so that no two dogs both have a loyalty score for the same person.

But you have not just a many-to-many, but one in which each pair of related entities has additional data associated with that relationship. That extra data does not belong exclusively to any dog or person, so you need an entity for it. Now your relationship graph looks like this:

Dog  <-- 1:many -->  DogPersonLoyalty  <-- many:1 -->  Person

On the Dog side, the mapped relationship is 1 Dog to many DogPersonLoyalty. You can map it as a Set<DogPersonLoyalty> as you suggest, but also as a List<DogPersonLoyalty> or, yes, a Map<some_key_type, DogPersonLoyalty>. There are advantages and disadvantages to each.

If you wanted to do it as a Map, then that might look like so:

@Entity
public class DogPersonLoyalty  {
  @ManyToOne
  @JoinColumn(name="dog_id")
  private Dog dog;

  @ManyToOne
  @JoinColumn(name="person_id")
  private Person person;

  private int loyalty;
}

@Entity
public class Dog  {
  // ...
  @OneToMany(mappedBy="dog")
  @MapKeyJoinColumn(name="person_id")
  private Map<Person, DogPersonLoyalty> loyalty;
}

Note: I've limited the annotations and annotation parameters to a minimal relevant set. I think it's all good, but you might need more to serve your full needs.

Which approach is more efficient and more often used (assuming there would be at most 100 key-value pairs in the map, so that the cost of accessing a value by key should be negligible)?

It depends a lot on how it would be used. If the database is as small as you say, then chances are that efficiency at the database is not a big driving force. But if you expect often to want to answer the question of what Lassie's loyalty to Timmy is, then there is much to be said for mapping the relationship as a Map.