At 06:12 AM 30/09/2004 -0300, you wrote:

> >Maybe you and we have different notions of "referential integrity". For
> >example, one would not create a table to implement declarative referential
> >integrity on a two or three state condition that won't change: one would
> >create a domain with a CHECK constraint.
> >
> >create domain d_paytype char(6)
> >CHECK (VALUE IS NULL OR VALUE IN('CASH', 'CHEQUE','CREDIT'));
> >
>
>Helen, maybe why have different concepts. AFAIK any data reusable must
>be in a table. Sound more logic to have that kind of items in tables and
>not *hardcoded* inside a check. What happens if i need to use a new kind
>of payment in both cases?

In that case, you need a table. But you don't *have* to declare
referential integrity for it - this is what I think Martijn is referring to
as "an Fb implementation problem".

>In your option, i need to go to the database and modify the domain and
>the constraint. My option, just insert a record.

Indeed; and if the values are not going to be a static set, the table
approach is the correct one.

>What happens if i need to use another database technology not supporting
>that kind of constraint? What happens if i need to use different names
>to the same type of payment, by example?

These are two separate problems.
-- In the first case, I think one always has to weigh up the relative costs
and benefits if one tries to take a generic approach to design. I think
you are in double-triple jeopardy if you aim to make a client/server
database with standard language characteristics behave like Access, a
file-server data storage system with its own peculiar language.

In the second case, where you might need to use different languages to
express the same "thing", it is not a good design strategy to make that
"thing" a key. A better alternative is to provide a surrogate key and
enable name and language changes to occur without breaking any
relationships. That makes the relationship more robust, can be implemented
in any DBMS and enables you to avoid declarative referential integrity if
there's dbms-specific reason to avoid them (as there is in Firebird with
small lookup tables and/or imbalance in the distribution of values in a
foreign key).

>Anyway, why that work fine in Access and not in Firebird? Something is
>wrong there.

What is "wrong" is that Access is architecturally a totally different beast
to Firebird. It stores indexes in database files.

>So you confirm that is the trouble? A few values inside the foreign
>keys?

Yes. Firebird uses binary tree indexes. When you get an index with only a
few values, or an index with many possible values but a distribution of
only a few values across the majority of nodes, the index forms what is
known as "long duplicate chains". This means the same value is spread
across many index pages (pages==blocks of disk on which data are stored)
and your searches get slower and slower.

>So the solution can be deleting that keys?

Yes. It is not the *key*, per se, that causes the trouble, it is the
mandatory automatic index that is created to support the key. So, if you
are not looking at static conditions that you can implement with check
constraints, then you are looking at implementing referential integrity
with custom triggers. This is simpler than you might think. The triggers
you write will be identical to the checks that the formal RI triggers
do: they are quite consistent for each table that you need to work
on. Yes, it does mean you have to do it for each of these low-number
lookup/control tables but you can write boilerplate code..

Once you get accustomed to the power of triggers, you might be less
inclined to treat it as a problem that you can't imitate everything that
Access does. Firebird isn't Access and it isn't *like* Access. The move
from a dbms that manages files to one that manages on-disk structures is
one that takes a lot of rethinking.

./heLen