> I was thinking the same. For example I have a customer
> with COUNTRY = 10, that numeric value is referencing a record
> in the COUNTRY table with the rest of the information about
> that country, the name and still other things.
> I think I'm in the same confusion like you

<...>

> I thought the relationships between customers and the six
> tables are master-detail relationships.

SQL (and therefore IB) does not distinguish between one form of
relationship or another. In SQL terms there is no such thing as a
lookup relationship, nor anything called master-detail. These are
simply terms we use to describe how we use the relationships in the
client application.

Which is why I added my clarification to what Helen said. I still
believe that FKs are good, you just need to be aware of IBs problems.
Note that the problem is not specifically to do with FKs, it is to do
with indexes. You can see exactly the same problem with other indexes
where low selectivity becomes a problem.

Imagine I have some customer table with a field customer_type, that
has 3 possible values; Big, Medium and Small. Because I think I
might add other types later I define these types in a separate table
used for lookups when editing a customer. If I tell IB about this
relationship it will create an index on the Customer table for use by
the FK (and it does not tell me about that index or give me a chance
to control it). The index is simply a definition like...
CREATE INDEX ON customers (customer_type);

Now imagine this database is used by a Microsoft division, so that
there are 3 million customers. That means the index will have three
entries with million record pointers for each - and the corresponding
number of database pages. Whenever IB has to add another record it
has to insert a new pointer into that collection of a million record
pointers - and it does not do this efficiently.


Notice that it would be possible to describe a similar situation in a
so-called master-detail relationship, where a master record has a
million detail records. The same problems would exist in this
situation and have to be dealt with.


However my main point is that you would probably not notice the
performance impact if this happened on a database with only 10
thousand customers. If it is not noticeable, then dont worry about
it. If it is noticeable then...

1. Define your own index with a higher selectivity,
CREATE INDEX ON customers (customer_type,customer_number)
2. Define the relationship constraints (cascading etc) using triggers.


HTH

Geoff Worboys
Telesis Computing