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.
- 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 of
transmitterchange (old one removed, sometimes a new one attached), or some samples taken.
I have repeated the date and monkey id on
transmittertable 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
samples taken table to make direct access of that data easy - or just use the JOIN to access it via