At 11:42 PM 16/03/2008, you wrote:

>> I don't like this design much because I don't like non-atomic indexes
>> and I don't like hierarchical primary keys. But it's a different
>> issue, I guess.
>
>Well I'm always interested to learn something new.
>
>In my case a SWIMRESULT record can have 0-n records in SPLIT
>(intermediate times), but only one for a certain DISTANCE.

Yes: but the PK of SPLIT is non-atomic: the DISTANCE key has meaning as data. From my puristic perspective, that is enough alone to warrant a surrogate key. And you are bumping into indexing issues, in addition, because you want SWIMRESULTID to be a foreign key.

>I always look at these split times as a collection of on SWIMRESULT at
>the same time, a collection of pairs distance / swimtime.

Add a generated surrogate field SPLITID to SPLIT and use a UNIQUE constraint to enforce the unique pairing of SWIMRESULTID and DISTANCE. Still place the FK (which is non-unique) on SWIMRESULTID. You can place another index on DISTANCE as well, if you need to.

>So you think the tables/fields should look different? Or the indexes?
>Or both?

See above. But I must say it's a bit puzzling why the same SWIMRESULT master would involve different distances in the detail...are the races handicapped on distance? Just curious...

./heLen