ORM setup - table relationships

Hibernate ORM
1

I am considering how to structure the relationships in my data and would like to know if this way is correct.

I have modelled in SQL initially then will progress to hibernate once it is correct.

MONKEYS

id name
1 Bob
2 Mary
3 John

MEASUREMENTS

id length width weight measure_date monkey_id health_check_id
1 12.4 6.3 3.2 2023-06-01 1 1
2 12.3 6.1 NULL 2023-06-01 1 1
3 12.1 6.2 NULL 2023-06-01 1 1
4 12.4 6.3 3.2 2023-06-01 1 2
5 12.3 6.1 NULL 2023-06-01 1 2
6 12.1 6.2 NULL 2023-06-01 2 3

SAMPLES TAKEN

id blood fur stool health_check_id
1 yes no no 1
2 no no no 2

TRANSMITTER

id channel tuning attached removed health_check_id monkey_id user_attaching
1 121 3 2023-06-01 2024-06-03 1 1 1
2 145 2 2023-06-10 NULL 2 1 2
2 117 1 2023-07-18 NULL 3 3 2

HEALTH CHECK

id monkey_id check_date user_holding user_measuring
1 1 2023-06-01 1 2
2 1 2023-06-10 2 1
3 3 2023-07-18 1 2

USER

id name
1 Clint Eastwood
2 Julia Roberts

Notes:

  1. All length measurements are taken 3 or more times and then averaged. The requirement is to store all 3 measurements.

Weights are only taken once (unlike measurements) and generally once the lengths are finished (so at a seperate stage (meaning I will probably end up with 3 rows of measurements and 1 row of weight per health check in the current schema.

  1. Multiple “events” can happen under a given health check (i.e. a set of measurements and weight, transmitter change (old one removed, sometimes a new one attached), or some samples taken.

  2. I have repeated the date and monkey id on measurements and transmitter table when they could be retrieved by a JOIN on health_check. This is because many queries will be to retrieve a view of a monkey and either its current weight, or a graph of weight over time etc. I can access measurements directly by repeating date and monkey id on the measurements table (same for transmitter).

I did wonder if each type of measurement should have its own table

A detailed monkey view in the UI would show a monkey entity with its fields and its related fields of channel, tuning, date of last health check, current weight, weight graph, length graph etc.

So the two main questions:

Should measurements/weights all stay on the same table?
Should I be repeating dates, and monkey_id on the measurements, transmitter and samples taken table to make direct access of that data easy - or just use the JOIN to access it via health check.

2

Should measurements/weights all stay on the same table?
Should I be repeating dates, and monkey_id on the measurements, transmitter and samples taken table to make direct access of that data easy - or just use the JOIN to access it via health check.

Personally, I would model this as a single table. You know that there “must be” 3 length measurements, so encode that into the schema.

@Entity
public class HealthCheck {
  @Id
  @GeneratedValue
  Long id;
  Instant checkDate;
  @ManyToOne(fetch = FetchType.LAZY)
  Monkey monkey;
  @ManyToOne(fetch = FetchType.LAZY)
  User userHolding;
  @ManyToOne(fetch = FetchType.LAZY)
  User userMeasuring;

  Measurement measurement1;
  Measurement measurement2;
  Measurement measurement3;
  Float weight;
  boolean blood;
  boolean fur;
  boolean stool;
}
@Embeddable
public class Measurement {
  Float length;
  Float width;
}
3

All length measurements are taken 3 OR MORE times.

So the measurements could be more than 3.

There is also no transmitter relationship in this model?

4

If there can be more measurements, then you’ll need a separate table indeed. Here a suggestion:

@Entity
@Table(name = "MONKEYS")
public class Monkey {
  @Id
  @GeneratedValue
  Long id;
  String name;
  @OneToMany(mappedBy = "monkey")
  Set<HealthCheck> healthChecks = new HashSet<>();
}
@Entity
@Table(name = "HEALTH_CHECKS")
public class HealthCheck {
  @Id
  @GeneratedValue
  Long id;
  Instant checkDate;
  @ManyToOne(fetch = FetchType.LAZY)
  Monkey monkey;
  @ManyToOne(fetch = FetchType.LAZY)
  User userHolding;
  @ManyToOne(fetch = FetchType.LAZY)
  User userMeasuring;
  @ElementCollection
  @CollectionTable(name = "MEASUREMENTS")
  List<Measurement> measurements = new ArrayList<>();
  Float weight;
  boolean blood;
  boolean fur;
  boolean stool;
  @Embedded
  TransmitterChange transmitterChange;
}
@Embeddable
public class Measurement {
  Float length;
  Float width;
}
@Embeddable
public class TransmitterChange {
  Integer channel;
  Integer tuning;
  Instant attached;
  Instant removed;
  @ManyToOne(fetch = FetchType.LAZY)
  User userAttaching;
}
5

So a monkey has transmitter 50 attached. Then later he has transmitter 50 removed and transmitter 51 attached.

With this model how is transmitter 50 recorded as removed?

6

Look, I don’t know your model well, so I can just propose suggestions based on what I understood.
If you know that this isn’t going to work, then model it differently.

I can answer targeted questions about how to model X or Y, but you will have to write the final entity model yourself.