ORM design for OneToFew

Hibernate ORM
1

Hi,

I have a design question. Best I start out with the entities.
It relates to monitoring some wild birds. When each bird is caught a microchip (PIT) is inserted under its skin.
99% of the time each bird only has 1 microchip, but sometimes when a bird is caught the microchip is missed and they insert a second one OR the chip falls out and they insert a second one.

If the tag is missed you end up with two bird records for one bird - issue #1
If the tag falls out you still only have one bird record, but its related to two tags - issue #2

If a bird ends up with two microchips they want to know about both, but the most recent one will be used going forward (it may be months before the duplicate mistake is realised and in the meantime two records are created for the same bird).

Note that microchips can be loaded into the db without being associated to a bird, so that relationship is not enforced.

BIRD

@Entity
@Data
public class Bird {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;
    private String name;

    @JsonIgnore
    @OneToMany (cascade = { CascadeType.DETACH, CascadeType.MERGE, CascadeType.MERGE, CascadeType.REFRESH}, orphanRemoval = true, mappedBy = "bird")
    private List<PIT> listPIT = new ArrayList<>();

  // two other fields also OneToMany similar to above
}

PIT

public class PIT {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;
    @Column(unique = true)
    @NotEmpty(message = "Name must not be null or empty")
    private String code;
    private LocalDate dateInserted;
    @ManyToOne(fetch = FetchType.LAZY, optional = true)
    private Bird bird;
}

I have setup the bidirectional relationships, but the question becomes is this the best structure?

100% of my DTO’s that retrieve a bird include returning the code of the PIT/microchip so it seems wasteful to constantly get a list of PIT tags and sort based on date to get the most recent one.
Is there a better way?

Merging, or setting up a relationship of the records (birds) when a duplicate is found is another issue.

2

IMO the best way to solve this would be to introduce a “currentPit” association on the Bird and work with that instead. You could maintain this through a trigger on the PIT table or manually in your code.
A possible alternative is to use lateral joins for this purpose i.e. bird b lateral join (select * from pit p where p.bird_id = b.id order by date_inserted desc limit 1) p on 1=1. Blaze-Persistence Entity-Views could be used to model this nicely in the DTO model:

    @EntityView(Bird.class)
    public interface BirdDto {
        @IdMapping
        Long getId();
        String getName();
        @Mapping("listPIT")
        @Limit(limit = "1", order = "dateInserted desc")
        PITDto getCurrentPit();

        @EntityView(PIT.class)
        interface PITDto {
            String getCode();
        }
    }