Automatic Querying a number of levels

[Shane]

I wonder if someone has any ideas about the following.

I am currently producing some reports for a manufacturing company who work
with metal.

A finished part can contain multiple sub-parts to make up the finished part.
The sub-parts can also be made up of sub-parts and those sub-parts can also
be made up of sub-parts etc etc.

All parts are contained within the same table and I have a seperate table
showing the sub-parts that each part is made up of. I can of course design a
certain number of queries but I need some solution that will in effect keep
querying the tables until there are no further sub-parts. If I design a
number of queries, then the number of parts is pre-defined i.e. If I design
five queries, I can go down five levels from the main part. The problem is
that there is no way of knowing how many levels to go down, so I thought
that some type of automatic solution that continued to produce the sub-parts
until there are no more sub-parts would be a good idea, if indeed this is at
all possible.

I am sorry if this is really confusing - I have tried to explain it as
simply as I possibly could.

Very many thanks,

Shane

[Alan Webb]

Shane,
There are three classical table structures for a bill of materials or other
hierarchical data problem. The first is a single table of parts and a
column in that table that lists the part's immediate parent. Then in a
query you join the table to itself so that you can list the details for
children, parents. This one is problematic because you have to know how
many self-joins to create to display the whole chain of parent/child parts
and requires a human or code to troll the hierarchy, count the levels
(number of joins) and build the SQL statement to return the requested
result. The second method is similar to the first but uses a two tables.
The first table is a parts master list that lists all parts and says nothing
about which is a component of what. The second table is the bill of
materials table and has two columns, child_part and parent_part. There may
be other columns in the bom table if it helps to record details about the
child/parent part pairing. This is better because it establishes a
many-to-many relationship between parts in the master list. But traversing
the hierarchy is still a non-trivial exercise without some help from code or
an analyst to create the needed SQL. The third is an expansion on the bom
table in method 2. It has as many columns in it as are needed to track all
levels of the hierarchy. It isn't strictly normal form but the payoff in
ease of querying is worth the breach in orthodoxy. You still have a parts
master and have to join the parts master to this multi-column bom. You need
as many rows in this table as needed to track the lineage of every part and
as many columns as there are levels in your hierarchy. The payoff is that
rolling-up costs for a chain of parts/components becomes a straighforward
group-by on the column in the bom that has the top-level part requested.
Updating the bom becomes easier as well since it's all laid out nicely in
this table. If down the road you find that the bom has additional levels
it's a pretty easy task to add as many columns as needed to track the added
levels.
'k. enough about that. Both Oracle 8 and I believe SQL Server 7 have SQL
keywords that are not ANSI standard but vastly simplify the task of querying
and reporting hierarchical data like a bill of materials. Oracle's is
CONNECT BY, I believe. SQL Server has ROLLUP, COMPUTE BY and CUBE. If you
have Access 2k or later and your sys-admin will let you install the Office
Server Extensions on a machine running Win NT 4.0, Win2k, Win2k Server or
Win XP, then you can run MSDE (Microsoft Data Engine, formerly Microsoft
SQL-Server Desktop Edition) and perhaps use ROLLUP. The caveat is that
your tables have to be designed so that ROLLUP will work as intended. Good
luck. Hierarchical data is an old & fun problem.
"Shane" <sh********@dsl.pipex.com> wrote in message
news:40***********************@news.dial.pipex.com ...

I wonder if someone has any ideas about the following.

I am currently producing some reports for a manufacturing company who work
with metal.

A finished part can contain multiple sub-parts to make up the finished part. The sub-parts can also be made up of sub-parts and those sub-parts can also be made up of sub-parts etc etc.

All parts are contained within the same table and I have a seperate table
showing the sub-parts that each part is made up of. I can of course design a certain number of queries but I need some solution that will in effect keep querying the tables until there are no further sub-parts. If I design a
number of queries, then the number of parts is pre-defined i.e. If I design five queries, I can go down five levels from the main part. The problem is
that there is no way of knowing how many levels to go down, so I thought
that some type of automatic solution that continued to produce the sub-parts until there are no more sub-parts would be a good idea, if indeed this is at all possible.

I am sorry if this is really confusing - I have tried to explain it as
simply as I possibly could.

Very many thanks,

Shane

[Shane]

Alan,

Thank you!

I'm glad in a way that you have answered as you have as it meant that I was
originally thinking along the right lines.

In short, my options are really creating the second child/parent table and
then writing as many queries as is necessary or creating a master table of
all possible child parts for each parent part. The only problem with this is
updating this table - My solution is an off-line solution querying a
SQL-based bespoke application and as soon as another part is added to their
inventory using whatever front end they currently use, I doubt if anyone is
going to remember to add an entry to the "master" table.

Anyway, that's my problem to sort - your reply has been most helpful and I
just wanted to acknowledge my thanks and appreciation to you.

Regards,
Shane Clark

"Alan Webb" <kn*****@hotmail.com> wrote in message
news:yO***************@news.uswest.net...

Shane,
There are three classical table structures for a bill of materials or other hierarchical data problem. The first is a single table of parts and a
column in that table that lists the part's immediate parent. Then in a
query you join the table to itself so that you can list the details for
children, parents. This one is problematic because you have to know how
many self-joins to create to display the whole chain of parent/child parts
and requires a human or code to troll the hierarchy, count the levels
(number of joins) and build the SQL statement to return the requested
result. The second method is similar to the first but uses a two tables.
The first table is a parts master list that lists all parts and says nothing about which is a component of what. The second table is the bill of
materials table and has two columns, child_part and parent_part. There may be other columns in the bom table if it helps to record details about the
child/parent part pairing. This is better because it establishes a
many-to-many relationship between parts in the master list. But traversing the hierarchy is still a non-trivial exercise without some help from code or an analyst to create the needed SQL. The third is an expansion on the bom
table in method 2. It has as many columns in it as are needed to track all levels of the hierarchy. It isn't strictly normal form but the payoff in
ease of querying is worth the breach in orthodoxy. You still have a parts
master and have to join the parts master to this multi-column bom. You need as many rows in this table as needed to track the lineage of every part and as many columns as there are levels in your hierarchy. The payoff is that
rolling-up costs for a chain of parts/components becomes a straighforward
group-by on the column in the bom that has the top-level part requested.
Updating the bom becomes easier as well since it's all laid out nicely in
this table. If down the road you find that the bom has additional levels
it's a pretty easy task to add as many columns as needed to track the added levels.
'k. enough about that. Both Oracle 8 and I believe SQL Server 7 have SQL
keywords that are not ANSI standard but vastly simplify the task of querying and reporting hierarchical data like a bill of materials. Oracle's is
CONNECT BY, I believe. SQL Server has ROLLUP, COMPUTE BY and CUBE. If you have Access 2k or later and your sys-admin will let you install the Office
Server Extensions on a machine running Win NT 4.0, Win2k, Win2k Server or
Win XP, then you can run MSDE (Microsoft Data Engine, formerly Microsoft
SQL-Server Desktop Edition) and perhaps use ROLLUP. The caveat is that
your tables have to be designed so that ROLLUP will work as intended. Good luck. Hierarchical data is an old & fun problem.
"Shane" <sh********@dsl.pipex.com> wrote in message
news:40***********************@news.dial.pipex.com ...

I wonder if someone has any ideas about the following.

I am currently producing some reports for a manufacturing company who work with metal.

A finished part can contain multiple sub-parts to make up the finished part.

The sub-parts can also be made up of sub-parts and those sub-parts can

also

be made up of sub-parts etc etc.

All parts are contained within the same table and I have a seperate table showing the sub-parts that each part is made up of. I can of course design a

certain number of queries but I need some solution that will in effect keep

querying the tables until there are no further sub-parts. If I design a
number of queries, then the number of parts is pre-defined i.e. If I

design

five queries, I can go down five levels from the main part. The problem

is that there is no way of knowing how many levels to go down, so I thought
that some type of automatic solution that continued to produce the

sub-parts

until there are no more sub-parts would be a good idea, if indeed this

is at

all possible.

I am sorry if this is really confusing - I have tried to explain it as
simply as I possibly could.

Very many thanks,

Shane

[Mike MacSween]

Try this I was involved in:

http://groups.google.com/groups?hl=e...oup%253Dcomp.*

I found three standard methods

Adjacency Lists
Nested Sets
Materialised Paths

and some that were variations on those

Searches on Google and in comp.databases and comp.databases.theory would be
a start. Nested Sets + Celko would get you the details on that method from
the horses mouth as it were.

If that link up there doesn't work try a 3 group search on:

recursive join - blind Alley
or
hierachical structure - an overview

together with my name.

HTH, Mike MacSween

"Shane" <sh********@dsl.pipex.com> wrote in message
news:40***********************@news.dial.pipex.com ...

I wonder if someone has any ideas about the following.

I am currently producing some reports for a manufacturing company who work
with metal.

A finished part can contain multiple sub-parts to make up the finished part. The sub-parts can also be made up of sub-parts and those sub-parts can also be made up of sub-parts etc etc.

All parts are contained within the same table and I have a seperate table
showing the sub-parts that each part is made up of. I can of course design a certain number of queries but I need some solution that will in effect keep querying the tables until there are no further sub-parts. If I design a
number of queries, then the number of parts is pre-defined i.e. If I design five queries, I can go down five levels from the main part. The problem is
that there is no way of knowing how many levels to go down, so I thought
that some type of automatic solution that continued to produce the sub-parts until there are no more sub-parts would be a good idea, if indeed this is at all possible.

I am sorry if this is really confusing - I have tried to explain it as
simply as I possibly could.

Very many thanks,

Shane

[Mike MacSween]

By the way, my 3 'methods' aren't the same as Alan Webb's 3 methods. Not
that his aren't perfectly valid approaches (though the first two seem to be
standard adjacency list or variations and the third looks frighteningly
un-normalised). But I didn't want you to get the idea that there are 3
'standard' solutions to this and that Alan and I aren't talking about the
same 3.

Yours, Mike
"Mike MacSween" <mi***********************@btinternet.com> wrote in message
news:40***********************@news.aaisp.net.uk.. .

Try this I was involved in:

http://groups.google.com/groups?hl=e...oup%253Dcomp.*
I found three standard methods

Adjacency Lists
Nested Sets
Materialised Paths

and some that were variations on those

Searches on Google and in comp.databases and comp.databases.theory would be a start. Nested Sets + Celko would get you the details on that method from
the horses mouth as it were.

If that link up there doesn't work try a 3 group search on:

recursive join - blind Alley
or
hierachical structure - an overview

together with my name.

HTH, Mike MacSween

"Shane" <sh********@dsl.pipex.com> wrote in message
news:40***********************@news.dial.pipex.com ...

I wonder if someone has any ideas about the following.

I am currently producing some reports for a manufacturing company who work with metal.

A finished part can contain multiple sub-parts to make up the finished part.

The sub-parts can also be made up of sub-parts and those sub-parts can

also

be made up of sub-parts etc etc.

All parts are contained within the same table and I have a seperate table showing the sub-parts that each part is made up of. I can of course design a

certain number of queries but I need some solution that will in effect keep

querying the tables until there are no further sub-parts. If I design a
number of queries, then the number of parts is pre-defined i.e. If I

design

five queries, I can go down five levels from the main part. The problem

is that there is no way of knowing how many levels to go down, so I thought
that some type of automatic solution that continued to produce the

sub-parts

until there are no more sub-parts would be a good idea, if indeed this

is at

all possible.

I am sorry if this is really confusing - I have tried to explain it as
simply as I possibly could.

Very many thanks,

Shane

[Alan Webb]

Mike,
Yup. A table with all the levels of the hierarchy and enough rows to map
all the leaves of the tree at the bottom of the tree isn't normal form. At
the risk of incurring the wrath of those that defend relational database
orthodoxy, sometimes the feature/time/cost equation means you go with what
works--even if it isn't an orthodox solution. I'll check out your 3, I've
only heard of the 3 I mentioned.

"Mike MacSween" <mi***********************@btinternet.com> wrote in message
news:40***********************@news.aaisp.net.uk.. .

By the way, my 3 'methods' aren't the same as Alan Webb's 3 methods. Not
that his aren't perfectly valid approaches (though the first two seem to be standard adjacency list or variations and the third looks frighteningly
un-normalised). But I didn't want you to get the idea that there are 3
'standard' solutions to this and that Alan and I aren't talking about the
same 3.

Yours, Mike
"Mike MacSween" <mi***********************@btinternet.com> wrote in message news:40***********************@news.aaisp.net.uk.. .

Try this I was involved in:

http://groups.google.com/groups?hl=e...oup%253Dcomp.*

I found three standard methods

Adjacency Lists
Nested Sets
Materialised Paths

and some that were variations on those

Searches on Google and in comp.databases and comp.databases.theory would

be

a start. Nested Sets + Celko would get you the details on that method from

the horses mouth as it were.

If that link up there doesn't work try a 3 group search on:

recursive join - blind Alley
or
hierachical structure - an overview

together with my name.

HTH, Mike MacSween

"Shane" <sh********@dsl.pipex.com> wrote in message
news:40***********************@news.dial.pipex.com ...

I wonder if someone has any ideas about the following.

I am currently producing some reports for a manufacturing company who

work with metal.

A finished part can contain multiple sub-parts to make up the finished

part.

The sub-parts can also be made up of sub-parts and those sub-parts can

also

be made up of sub-parts etc etc.

All parts are contained within the same table and I have a seperate table showing the sub-parts that each part is made up of. I can of course design

a

certain number of queries but I need some solution that will in effect

keep

querying the tables until there are no further sub-parts. If I design a number of queries, then the number of parts is pre-defined i.e. If I

design

five queries, I can go down five levels from the main part. The problem is that there is no way of knowing how many levels to go down, so I

thought that some type of automatic solution that continued to produce the

sub-parts

until there are no more sub-parts would be a good idea, if indeed this

is

at

all possible.

I am sorry if this is really confusing - I have tried to explain it as
simply as I possibly could.

Very many thanks,

Shane