Helen Borrie wrote:

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

Is it possible to prepay for the book and get the pages which are done?

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

The tax office ? :-))

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

If they delete the tuple Order_Pos gets a negative value (trigger +
procedure) and the
field DELETED gets True. ORDERPOS_OLD only holds the original value of
ORDER_POS.
That way I free the ORDER_POS number. It doesn't happen often, but it
does.

I have two tables (Work and ForeignFirm) who have a foreign key
(ORDER_ID, ORDER_POS) to Parts, with ON UPDATE CASCADE.

But it is possible that the user can change the ORDER_POS.
And therefore I will change the structure.
Yes, you convinced me. :-)

But which model is better?

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
);
CREATE UNIQUE INDEX UIDX_P_ORDER_ID_POS ON PARTS (ORDER_ID, ORDER_POS);
CREATE INDEX IDX_P_DRAFT_NO ON PARTS(DRAFT_NO);

Or


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


Regards,
Jörg Schiemann