I’m designing a database schema to perform calculations related to crops, and I’ve encountered some complexities at the application level, particularly with Hibernate ORM. Here’s the DDL for the relevant tables:

create table crops
(
    has_canopy boolean not null,
    id         bigserial primary key,
    name       varchar(255),
    plant_date varchar(255),
    region     varchar(255)
);

create table base_crop_coefficient
(
    kc_dev  double precision not null,
    kc_end  double precision not null,
    kc_init double precision not null,
    kc_mid  double precision not null,
    crop_id bigint not null primary key references crops
);

create table canopy_crop_coefficient
(
    canopy  integer not null,
    kc_dev  double precision not null,
    kc_end  double precision not null,
    kc_init double precision not null,
    kc_mid  double precision not null,
    crop_id bigint not null references crops,
    primary key (canopy, crop_id)
);

create table crop_growth_stage
(
    l_dev   integer not null,
    l_init  integer not null,
    l_late  integer not null,
    l_mid   integer not null,
    crop_id bigint not null primary key references crops
);

The canopy of a crop, expressed as a percentage, indicates how much of the ground is covered by the crop’s leaves. However, not all crops are canopy-specific, so instead of making canopy a nullable column, I created a separate table, canopy_crop_coefficient.

In this schema:

• The relationship between crops and base_crop_coefficient is one-to-one.
• The relationship between crops and canopy_crop_coefficient is one-to-many.
  Here’s the corresponding Crop entity in my Hibernate application:

@Entity
@Table(name = "crops")
public class Crop {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String region;
    private String plantDate;
    private boolean hasCanopy;

    @OneToOne
    private BaseCropCoefficient baseCropCoefficient;

    @OneToMany
    private List<CanopyCropCoefficient> canopyCropCoefficients;
}

The problem I’m facing is that when I query for Crop entities, either baseCropCoefficient or canopyCropCoefficients will not be present, depending on the type of crop. This seems to conflict with my initial database design goals, where I wanted to avoid such conditional presence in the same entity.

Given this scenario:

How can I better handle these conditional relationships?

Should I consider a different approach to my database schema or entity mapping?

Any advice or best practices would be greatly appreciated!