At 11:21 AM 23-02-01 +0100, you wrote:

>Hi Helen,
>
>thanks for your effort.
>I hope my questions are not to stupid.

Not stupid, just time-consuming. :)) I'd better write a handbook....

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

So how do you relate products and stock items to your ordering system?

>BTW, what means BOM?

Bill of Materials - my assumption, based on your other posting.

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

Language is language. Your PARTS table looks like an Order Detail table to me... In English, "Parts" is usually the table which holds the descriptions of parts of an assemblage.

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

That's cool, very satisfactory. :))

>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 depends. I had assumed that your 'POS' was some kind of sequencing or proritising mechanism (given that I'd assumed we were still talking about works orders, where we care about the order in which the BOM items are delivered to the factory). If it is serving no other function except to make a "part" unique, it should be OK.

That said, I'm a bit suspicious about that ORDER_POS_OLD. Does that mean that ORDER-POS *is* significant and can be changed after the "parts" have been created? In that case, I would want the stability of a unique surrogate PK. I don't like structures where humans are capable of bending keys.

>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 ? ¿-?

You are forcing the creation of this index by making it your primary key. If it were not your primary key but you NEEDED an index across these two columns - I don't know why you would need it - then a composite index would be OK.

If you needed an index on ORDER_POS, you would **still** need to involve ORDER_ID, to give selectivity to the index. But, in this case, the index would be (ORDER_POS, ORDER_ID).

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

No. The unique constraint isn't an index, so it won't cause problems for the optimizer.

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

No, it doesn't...well, it kinda does...but it's an internal structure and it won't be used by the optimizer for joins.

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

Yes. In your PARTS example here, the composite key would be atomic if both columns had no significance other than to make the row unique. But if ORDER_POS can be changed for some reason, then it is NOT atomic.

>Has a PK to be atomic?

For integrity, yes. Will it work if it's not atomic? Yes.

Should I use a parachute when I plan to jump out of an aircraft at 7000 M? Yes. Could I jump without a parachute? Yes.

Regards,
Helen

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