Subject Re: [ib-support] Error:Object is in use
Author Helen Borrie
At 01:25 PM 22-02-01 +0100, you wrote:
> > I think it is very bizarre to make a composite primary key on the detail table AND to make one of the columns of that key your foreign key. What is your reasoning for that? What does WONO add that BMNO doesn't do on its own?
>WONO adds referential integrity. It guarantees that a record in
>BILLOFMATERIAL having a non-existent WONO will never be part of the

Hey, I'm not suggesting WONO shouldn't be there - of course it should. It just oughtn't to be forming part of the primary key.

>if I'm not mistaken, this is the "classic" (maybe obsolete) way of
>structuring master/detail relationships. It may not be desirable for
>performance and/or strictly firebird-related reasons, but from a design
>point of view I don't see it as bizarre as you do.

It's nothing to do with "Firebird reasons" and it's "theoretical", rather than "classic". I would call it the "file-based" way of structuring m/d - it's theoretically correct for a database like Paradox, which uses cumulative keys to form nested relationships, i.e. it doesn't use foreign keys natively.

I'm not saying this interleaved approach *breaks* anything, although (without trying it) I suspect it could get in the way of cascades. But it's bizarre, because the detail structure doesn't *need* a composite key and, since the WONO column defines the foreign key, it's redundant as part of the primary key.

Sometimes I think people do this because they follow Borland's example in the MastSQL sample database. That database is a dreadful model. It is a straight imitation of the Paradox sample, which commends it very little - it's not even a good Paradox design. But that's where those complex keys came from. [[ Note also the lack of generators and the use of numerics for primary keys in those tables which have simplex PKs... ]]

Break the shackles and clear out redundant garbage, I say.


All for Open and Open for All
InterBase Developer Initiative ยท