Re: Nested Tables - Why?

From: Randy DeWoolfson <randy_at_euclidsys.com>
Date: Tue, 06 Jul 1999 19:42:39 -0400
Message-ID: <3782946F.49A641C4@euclidsys.com>

do you mean a self-referential table? or are you questioning the use of a select statement in the from clause?

things to think about while costing in a BOM app:

1. are there multiple vendors for a given part? (can i buy this part from more than 1 guy?)
2. does the BOM allow for quantity pricing? (if i buy 100 will the price change?)
3. is there any dependent relationship between parts for price breaks. (if i buy 2 of those i can get 1 of these at half price)
4. do you use the same parts in a subassembly from a vendor that you also use directly (therefore different costs). (some resistor costs me $1 in my assembly but it comes as part of that subassembly i buy for $1)...

i think in a good BOM, you should have a self-referential table that describes the part hierarchy:

like

PART

---

part_id
component_part_id
part_name
part_no
etc..

where component_part_id = another part_id... then use CONNECT BY in your queries.

and other related tables for VENDORS, VENDOR_PART_PRICING, ENGINEERING_DRAWING, CHANGE_ORDER_HISTORY, PURCHASE_HISTORY etc.

if you need to price out a BOM then you really need to think about the whole AR/AP function, and you also need to think about your engineers, and how they will continuously change the BOM and its vendors.

Hth,
randy

Rob Edgar wrote:

> I have been looking at nested tables for one particular situation I have but
> I must say despiter reading the docs I still dont see WHY anyone would use a
> nested table.
> I mean basically they dont seem to me to be any differeent from a
> master/detail relationship between two traditional relational tables EXCEPT
> they are now harder to query or update and I assume will be much slower to
> query.
>
> Assuming there is something I am not getting here I wondered if someone
> could enlighten me to the benefits of using a nested table.
>
> The situation I have is that I am storing costs associated with a bill of
> materials for certain products and it seemed like the alternatives were a
> master "product" table and a detail table containing all the associated
> costs linked by productid to the master table OR to use a nested table
> "cost" column in the master table. It will be very rare that the data is
> edited as fresh cost entires will be added into the table each month from
> another table rather than editing this table. Queries will usually want to
> total the cost or to display them for detailed reports.
>
> Thanks
> Rob
Received on Tue Jul 06 1999 - 18:42:39 CDT