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:
- 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.
-
Multiple “events” can happen under a given
health check
(i.e. a set ofmeasurements
and weight,transmitter
change (old one removed, sometimes a new one attached), or some samples taken. -
I have repeated the date and monkey id on
measurements
andtransmitter
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
.