Hi Helen,

thanks for your effort.
I hope my questions are not to stupid.

The tablename material is no good choice I think. They don't have a depot.
If they need raw material in order to milling something they order it.
I see no need for three tables.
BTW, what means BOM?

Part is better, I think. But I'm not sure ¿-?

Here are my tables.

CREATE TABLE ORDER(
ORDER_ID D_ORDER_ID,
ORDER_NO D_ORDER_NO,
ORDER_NO_OLD D_ORDER_NO,
CUST_ID D_CUST_ID,
CUST_ORDER_NO D_CUST_ORDER_NO,
CONTACT_PERS D_NAME,
DATE_ARRIVED DATE NOT NULL,
DATE_DELIVERY DATE NOT NULL,
WEEK_DELIVERY INTEGER NOT NULL,
.
.
.
DELETED D_BOOLEAN,
CONSTRAINT PK_ORDER_ID PRIMARY KEY (ORDER_ID),
CONSTRAINT UK_ORDER_NO UNIQUE (ORDER_NO),
CONSTRAINT FK_ORDER_CUST_ID FOREIGN KEY (CUST_ID) REFERENCES
CUSTOMER (CUST_ID)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
CREATE INDEX IDX_ORDER_DATE_DELIVERY ON ORDER (DATE_DELIVERY);
CREATE INDEX IDX_ORDER_WEEK_DELIVERY ON ORDER (WEEK_DELIVERY);
CREATE INDEX IDX_ORDER_CUST_ORDER_NO ON ORDER (CUST_ORDER_NO);

They never see ORDER_ID. I use it so they can delete an order without
really deleting it, and they can use the deleted Order_No for a new order.
They are so afraid of the tax office. They say, the tax office wants
continuously numbers.
After 'deleting' an order the order_no changes to a negative value. (Trigger
+ Generator)

CREATE TABLE PARTS
(
ORDER_ID D_ORDER_ID,
ORDER_POS D_ORDER_POS,
ORDER_POS_OLD D_ORDER_POS DEFAULT 0,
DRAFT_NO D_DRAFT_NO NOT NULL,
APPELLATION D_APPELLATION NOT NULL,
AMOUNT_ORDERED INTEGER NOT NULL,
.
.
.
DELETED D_BOOLEAN,
CONSTRAINT PK_PARTS_ID PRIMARY KEY (ORDER_ID, ORDER_POS),
CONSTRAINT FK_PARTS_ORDER_ID FOREIGN KEY (ORDER_ID)
REFERENCES ORDER (ORDER_ID)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
CREATE INDEX IDX_P_DRAFT_NO ON PARTS(DRAFT_NO);



I try to explain with an example.
The firm gets an order to produce 120 arbors, 50 flanges and 90 bolts.
This is one order with 3 different parts.

This will be one tuple in the order table and 3 tuples in the parts table.
120 arbors becomes Order_Pos 1
50 flanges becomes Order_Pos 2
90 bolts becomes Order_Pos 3.

If I use a PK like PART_ID and an unique index on ORDER_ID and ORDER_POS,
what do I win except for an extra field?

It is maybe no good idea do have an contigued index on ORDER_ID and
ORDER_POS,
because the maximal ORDER_POS is mostly between 1 and 10 ? ¿-?

If I create a parts table like
CREATE TABLE PARTS
(
PARTS_ID D_PARTS_ID
ORDER_ID D_ORDER_ID,
ORDER_POS D_ORDER_POS,
ORDER_POS_OLD D_ORDER_POS DEFAULT 0,
DRAFT_NO D_DRAFT_NO NOT NULL,
APPELLATION D_APPELLATION NOT NULL,
AMOUNT_ORDERED INTEGER NOT NULL,
.
.
.
DELETED D_BOOLEAN,
CONSTRAINT PK_PARTS_ID PRIMARY KEY (PARTS_ID),
CONSTRAINT FK_PARTS_ORDER_ID FOREIGN KEY (ORDER_ID)
REFERENCES ORDER (ORDER_ID)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT UK_ORDER_ID_POS UNIQUE (ORDER_ID, ORDER_POS)
);
CREATE INDEX IDX_P_DRAFT_NO ON PARTS(DRAFT_NO);
CREATE INDEX IDX_P_ORDER_ID ON PARTS(ORDER_ID);

Or is the index IDX_P_ORDER_ID senseless because of UK_ORDER_ID_POS?

I still don't know if IB creates an index on unique, I think so, but I'm not
sure. ¿-?

You wrote that OrderNo + Position is *not* an atomic key,
can a composite key be atomic?

Has a PK to be atomic?


Best regards,
Jörg Schiemann

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
Sent: Thursday, February 22, 2001 11:41 PM
Subject: Re: [ib-support] Error:Object is in use


> 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