At 02:56 PM 22-02-01 +0100, you wrote:

>Helen Borrie wrote:
> >
> > At 12:12 PM 22-02-01 +0200, you wrote:
> > >I have also tried to completely rename the tables and fields by adding
> > >a 1 to each name that still does not work. This leads me to
> > >conclude that somewhere there is a serious error in my database.
> > >
> > >For the time being I am going to recreate my database and pump
> > >the data over while it still works. I would still appreciate
> > >comments suggestions as to how to prevent/fix this.
> > >
> > >Thanks
> > >Gerhardus
> >
> > 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?
> >
> > Helen
> >
>
>Why is that bizarre?
>I sometimes do create master-details the same way.
>I.e. I wrote an application for a mechanical engineering firm.
>The master table 'Order' has the field 'OrderNo' as primary key
>and the detail table 'Material' has a composite primary key
>composed of 'OrderNo' and 'Position' and the foreign key is 'OrderNo'.
>If I should use a primary key for the detail table like ie 'MaterialId'
>it would mean one extra field, because I can't relinquish the field
>'Position'.
>
>Does it matter for performance reason?

Without knowing how you use the detail table here, my comment is that, unless the Position column points to your material master table, this is a faulty relationship. I consider that OrderNo + Position is *not* an atomic key, since Position (I assume) is exercising the function of sequencing a material in the BOM. If there is no MaterialID, I can't see how you link the Materials list to your Material master table.

I would expect something like this:

Table Order - PK Order No.
Table MaterialMaster - PK MaterialID
Table BOM - PK BOM_ItemID, with OrderNo as FK to Order and columns MaterialID and Position constrained by a UNIQUE index. A lookup link (implicit foreign key, not implemented by a FOREIGN constraint, for selectivity reasons) between MaterialID in the BOM and MaterialID in the Material master...

IOW, this is exactly the case where a surrogate key is called for. But I state this without seeing how you link Orders with your Materials master.

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________