Re: BOM application

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<arobtl$cj8$2$8300dec7_at_news.demon.co.uk>...
> Object types will almost certainly make things more complicated
> in the database - although you might use memory variables of
> object types and then unpack them into relational tables.
>
> Look at the CONNECT BY query - it is more or less designed
> for BOM reporting.
>
> Sequence is probably better than sys_guid() - but I tend
> to prefer meaningful keys to synthetic ones.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>

>
> John wrote in message ...
> >Hi, gurus
> >Data of engineering Bill Of Materials are very hierarchical, nested,
> and
> >complicated.
> >I'd appreciate your comments on followings:
> >
> >(1) Will Object-Relational features of Oracle (VARRAY, NESTED TABLE,
> ...) be
> >a help?
> > Or, will those features make things more complicated?
> > What about querying complexity and performance of those
> features?
> >
> >(2) Are there any tools that allow designers and developers of
> relational
> > DBMS for developing hierarchical data application (complicated,
> nested
> >data, e.g., BOM)
> >
> >(3) sys_guid()? or sequence? for primary keys
> > Which should I use and when? Any pros and cons?
> >
> >Much thanks in advance,
> >--
> >John
> >

John, I would avoid using objects in the database when working with BOM type structures though you may want to use them in you application layer.

BOM backgroud for anyone interested:

It is fairly normal practice when building a BOM in a relational db to create the part table with multiple row types, that is, the table may hold model, group, assembly, and part rows. Then based on the type of row certain columns will have data and others will always be null because they only hold data for a specific row type (part, assembly).

Then the BOM itself contains the recursive structure that associates the part table entries to a Model which explodes into groups which in turn becomes assemblies which are made up of a series of parts. The connect by clause as Jonathin said is normally used to deal with BOM structures.

In many if not most manufacturing systems some form of Material Bill of Material will also exist.

The following text was one of the primary texts recommended by the American Production and Inventory Control Socity, APICS, back around 1990 - 1995 and if available has a good deal of information on the topic: Material Requirements Planning by Dr. Joseph Orlicky, McGraw Hill.

HTH -- Mark D Powell (CPIM, CIRM, OCP) -- Received on Sat Nov 23 2002 - 18:59:33 CST