> -----Ursprüngliche Nachricht-----
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] Im Auftrag von Helen Borrie
> Gesendet: Montag, 18. April 2005 13:54
> An: firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] Query optimization help
>
>
> Another thing to consider is how seriously you need that FK
> relationship. If it's just for validation, you can avoid
> defining the FK altogether and do the validation in a trigger
> instead. In situations where the "lookup table" is very
> stable, you tend to trade off a lot of performance for little
> or no gain in RI protection. If you feel you must have that
> RI protection and "action effects", again it can be done with
> triggers.

A FK is a declarative way of defining relationships between tables. For example, I'm using an ORM-tool which automatically maps relations between tables to 1:n and n:m class collections.

Enforcing those relationships may not be the primary priority when creating FKs, only declaring them. Yes, one could do the RI with triggers written in honkey-donkey SQL (SQL is so good for querying data, and so bad for procedural programming), but if you are giving the advice to drop FKs and maintaining the relationships manually with triggers (or better: in the application logic!?), then I have to ask: What for are SQL-Databases designed, and why do I use them?

> Note that we're not talking here about *all* FK
> relationships, only those that reference very small lookup
> sets, or that reference larger lookup sets but, in practice,
> utilise only a small number of the actual possible values.

In any case: There *is* a relationship between two tables, so lets declare this relationship. One could simply use an int without any FK and simply map it to values in the application logic (kind of using enums), but that's not exactly the idea of a RDBMS. Database schemas should be self-descriptive, and one common way to achieve that is using FKs.

> My classic example here was an electoral application that
> collected "Country of birth" in the elector profile. This
> was linked to all of the countries in the world (around 370
> at the time) but 85% of the electors were born in the country
> that was collecting and storing the data!! Country codes at
> the time were not changing much, so, given the distribution
> of the data, this FK wasn't justified.

Hey, I'm doing this too. With countries and languages. Why should I make the relation between attributes on one table and the values on another table secret/hide them? I think the fault is clearly because Firebird must choose between two indices and cannot decide which one to use (or decides in a sub-optimal way). However, dropping FKs only for the sake of speed is definitively the wrong way.

> There is idealism (pure relational theory) and there is
> pragmatism (taking on board real-world issues like data
> distribution and the degree to which an engine can implement
> one ideal without compromising other ideals too much).
> Designing well-performing structures is a matter of balancing
> ideals (which are not concerned with performance) with
> reality (which usually is!)

It's a matter of how good an RDBMS can transform my relational theory in practical computing. If using a PLAN-clause is the only way to get enough speed, then that is still a thousand times better than dropping FKs. You need to balance between theory and real-world if you have normalized the whole schema to the 5th degree, but in a well designed, well normalized application, there *are* FKs with low selectivity, and that is in no way wrong, and there is no reasong to drop them.

Regards, Alex