Subject RE: [firebird-support] Re: FK confusion
>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.

You have no idea<g>. There are about 60 tables. Some are much worse.

>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.

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

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.

>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.

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.

Thanks to both you and Adam for your responses, and I expect I'll be back
with more questions before this is all over.

Michael D. Spence
Mockingbird Data Systems, Inc.

[Non-text portions of this message have been removed]