Subject RE: [firebird-support] Re: FK confusion
Author Helen Borrie
I wrote

> >To add to Adam's comments, those hierarchical Paradox keys should be
> >high on your conversion hit-list. Paradox doesn't support foreign
> >keys, which is why you had to use hierarchical PKs to implement
> >referential integrity. With declarative RI, the unique ID from the
> >parent is all you need to store in the children of a hierarchical
> >relationship tree and it is needed only in the immediate child, not
> >in grandchildren, etc.

At 11:25 AM 11/07/2006, you wrote:

>Actually, Paradox has had RI since about version 5 or so, but it's not
>often used and was not retrofitted here.

You mistook what I said. Paradox doesn't have declarative RI. It
uses hierarchical indexes to implement it. Actually it goes back
further than Pdx 5 and the Windows era. It was in v.4.0 and, if
memory serves me correctly, in 3.5 as well. This works OK for
Paradox because its "constraints" are implemented via physical index
files and the whole design presumes these files are loaded into and
unloaded from (a very small) memory space as and when required and,
by design, without multi-user contention, i.e. for a model that has
virtually nothing in common with the Firebird model.

>One of the reasons for doing this conversion is because without any
>RI at all, the data has lots of warts and potholes. Not to mention
>the fact that the tables get corrupted for no apparent reason from time
>to time.

There's always a reason for everything. With the Paradox engine (the
BDE) there is immense fragility in the maintenance and healthy
survival of lock files.

> >
> >The performance and complexity effects from proliferating redundant
> >key elements in a database that does not depend on physical record
> >position (such as Firebird) will be killers until you address them.
> >
> >./heLen
>I'm aware of these issues, but since there are 1000+ source modules I
>have no hope of making any substantive changes to the DB design in time.
>There are even more urgent problems: The app relies heavily on QBE,
>which I have no choice but to rewrite in SQL.

Yup, you really have taken on the task of converting lead to
gold. To use another squirmy metaphor, don't ignore the need to stop
for petrol.