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

vladimir konrad wrote:

> The system I am developing has to handle "tests" (for rowing athletes):
> 1. how many meters athlete did in 10 minutes (result is float)
> 2. how long it took the athlete to do 5 kilo-meters (the result is time)
>
> So it looks that I need table for each of 1 and 2 (because of different
> data types describing the test).
>

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.

Personally I would think that one table that has the athlete's id and a
date (or timestamp to allow more than one a day) of the event as well as
a time interval and distance would suffice.

For 1. the time interval would always be 10mins, for 2. the distance
would always be 5km. To get individual test stats you can use WHERE
time=10mins or WHERE distance=5.0

You could even create test_views with the where clause pre-determined.

Worst case would be another column flagging the test type.

From there you can also add in a 5, 15, 20, 30, 40 minutes or even 2,
2.5, 7.5, 10 km tests as well without changing your structure.

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.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz