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

Hello,

>> vladimir konrad wrote:
>>> I think that I understand basic relational theory but

> Clearly, you'll have to revisit that thought.

Usually I have one table per "entity" modelled (and the table holds
fields describing that entity).

E.g. subject would have name fields and date of birth field (fields
related directly to subject), postal_address would be separate table and
subject_postal_address would be linking table between postal_address
and subject:

subject <-- subject_postal_address --> postal_address

This way, the postal_address can be made unique (with constrains) and
linked to other entities where the postal_address is needed.

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).

> The math beneath this is that query complexity goes up like O(E!A!V!)
> for Entity, Attribute and Value.

Well, when I thought about it, the gut feeling was that I am opening a
can of worms - it would push the complexity into code (the bad place to
have it in).

> The first price, though, and by far the biggest, is that it's
> impossible to maintain any kind of data integrity in such a system, as
> such constraints, by their nature, are application-dependent. Two
> applications means you're violating the SPOT (Single Point of Truth)
> Rule, and that in turn means your data turns quickly into
> incomprehensible gibberish.
It could be implemented inside of the database server (stored
procedures, views and such), but it would still be complex, hard, long
and as you said badly performing (your point about complexity made
that clear).

Vlad