Re: [OT] "advanced" database design (long)

Hello, and thanks

> Are the tests that different that you need to segregate the data?
> I see them both as being the time taken to travel a distance. The
> only difference is whether the time or distance is used to end the
> measurement.

Good point (I have realised this after posting, when I dug deeper into
the design). What I got now looks like this (linking fields omitted):

[subject]
1
|
n
[ergo: distance(float), time(interval), taken_on(date)]
n n
| |
1 1
[ergo_rate] [ergo_set]

The same test can be taken at different rates (e.g. 20 strokes per
minute), therefore the [ergo_rate] (there are only few rates they
always use).

The [ergo_set] determines what value was "set" before the test (bad
name but maybe i think of something better). So, it the distance is
set, it points to (1, 'distance') in [ergo_set]; if time, then to (2,
'time'). User chooses what is "set" before recording the test.
Also it is possible to ask "give me all 2000m ergo test results done
at this rate".

> Worst case would be another column flagging the test type.

Why do you think this is the worst case?

> I also see multiple samples for a single run. Time at 1km, 2km, 3km,
> 4km, 5km (or at 2min, 4 min, 6min...) - you could see whether they
> can maintain the speed over the distance or at what distance/time
> they wear out and slow down. (maybe they give too much in the first
> 2km so that they just crawl in the last 2)
> Maybe sub-times can be a second table.

They call it splits (have to check if it is done for ergos but it is
definitely done for racing on the water).

In ergo case, I would have extra table [ergo_split]:

[ergo] <-- [ergo_split: distance(float), clock(time)]

(they record the time a watch shows them, therefore time and not
interval)

Vlad