Subject Re: [firebird-support] Re: FK confusion
Author Helen Borrie
At 09:18 AM 11/07/2006, you wrote:
> >
> > > Except for the odd primary key, I guess...
> >
> > History is the millstone around my neck too. The design was
>originally for
> > Paradox for DOS. That primary key is not the only thing on the list
> > of changes, but I have to get it converted as-is first. The only
> > thing that's saving me is that this is a relatively tiny database,
> > so even TTable analogs will probably work well enough to get by.

At 09:18 AM 11/07/2006, you wrote:

>Firebird does not store records in order of primary key, so there is
>no performance benefit in setting particular fields as the primary key
>just because that is how you report on it.

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.

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.