vehicle to autoparts relationships

[javelin]

I posted an answer to someone's question, and realized I have more
questions than answers. Thus, I am going to post my scenario to get to
the question that I have:

I have a challenge, to figure out what part of the vehicle to relate
parts to. I can't relate a part to the entire vehicle. Why? Well, a
vehicle can have, apparently, more than one engine configuration. For
example, I have a Honda Civic with a 1.5L engine, and it can come with
a 1.6L engine as well. Now, I don't know that much about auto
mechanics, but my understanding is that you can have some similar parts
for those two engines, and some different parts. Thus, I believe I need
to relate the parts to the engine configuration. However, that's only
for engine parts. There are also transmission parts. The car can come
in manual or automatic, so now you have different parts that relate to
the transmission on this particular vehicle. Going further (and getting
funner), your wheel base affects the parts you have, and then there's
the body style. Thus, you need to relate the part to the particular
application, not just the vehicle.

All this to ask if my thinking is correct: is it best to create an Xref
table between each autopart category (engine, transmission, exhaust,
body, etc) and the parts table, or one large xref table between the
two, with the addition of a category field. The one large xref table
would have it's foreign key related to each of the tables that govern
their group (e.g.: tbl_engine.engine_id,
tbl_transmission.transmission_id, etc)

Thanks in advance for the advice.

[Aloha Kakuikanu]

javelin wrote:

I posted an answer to someone's question, and realized I have more
questions than answers. Thus, I am going to post my scenario to get to
the question that I have:

I have a challenge, to figure out what part of the vehicle to relate
parts to. I can't relate a part to the entire vehicle. Why? Well, a
vehicle can have, apparently, more than one engine configuration. For
example, I have a Honda Civic with a 1.5L engine, and it can come with
a 1.6L engine as well. Now, I don't know that much about auto
mechanics, but my understanding is that you can have some similar parts
for those two engines, and some different parts. Thus, I believe I need
to relate the parts to the engine configuration. However, that's only
for engine parts. There are also transmission parts. The car can come
in manual or automatic, so now you have different parts that relate to
the transmission on this particular vehicle. Going further (and getting
funner), your wheel base affects the parts you have, and then there's
the body style. Thus, you need to relate the part to the particular
application, not just the vehicle.

All this to ask if my thinking is correct: is it best to create an Xref
table between each autopart category (engine, transmission, exhaust,
body, etc) and the parts table, or one large xref table between the
two, with the addition of a category field. The one large xref table
would have it's foreign key related to each of the tables that govern
their group (e.g.: tbl_engine.engine_id,
tbl_transmission.transmission_id, etc)

Thanks in advance for the advice.

First, let me deviate into the area of guns because the assembly
diagram is more manageable there. In order to make things visual, I
suggest the following example:

http://www.kel-tec.com/su16aparts.html

You might heard of that relational database stores the whole rifle
diassebled completely as this diagram suggests:

table Parts (
partNo integer,
...
);

Contrary to what object people may say that assembling and
disassembling things within a computer environment is not a big deal.
It is certainly not a reason to dismiss relational approach.

Now, there are some larger parts, ggregated from the smaller ones, for
example, trigger assembly, or stock. How do we handle these? Simple,
they are just sets:

table AssemblyParts (
assemblyPartNo integer, // informally it is a set#
partNo integer, // foreign key to Parts
);

Granted, some of the interested queries become set joins, so you have
to be familiar with the concept of set join. Other than that I don't
see any problems. Do you?

[David Cressey]

"javelin" <go*************@spamgourmet.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

I posted an answer to someone's question, and realized I have more
questions than answers. Thus, I am going to post my scenario to get to
the question that I have:

I have a challenge, to figure out what part of the vehicle to relate
parts to. I can't relate a part to the entire vehicle. Why? Well, a
vehicle can have, apparently, more than one engine configuration. For
example, I have a Honda Civic with a 1.5L engine, and it can come with
a 1.6L engine as well. Now, I don't know that much about auto
mechanics, but my understanding is that you can have some similar parts
for those two engines, and some different parts. Thus, I believe I need
to relate the parts to the engine configuration. However, that's only
for engine parts. There are also transmission parts. The car can come
in manual or automatic, so now you have different parts that relate to
the transmission on this particular vehicle. Going further (and getting
funner), your wheel base affects the parts you have, and then there's
the body style. Thus, you need to relate the part to the particular
application, not just the vehicle.

All this to ask if my thinking is correct: is it best to create an Xref
table between each autopart category (engine, transmission, exhaust,
body, etc) and the parts table, or one large xref table between the
two, with the addition of a category field. The one large xref table
would have it's foreign key related to each of the tables that govern
their group (e.g.: tbl_engine.engine_id,
tbl_transmission.transmission_id, etc)

Thanks in advance for the advice.

Parts can have parts. Read up on "parts explosion".

[Neo]

I have a challenge, to figure out what part of the vehicle to relate parts to ...

You will need a schema where parts can have parts recursively.

Below is a dbd example that models parts that make up a Civic LE and
Civic SE. Each car has a different engine configuration and
transmission however some parts like starter and gears are shared.
Queries return each car's parts by traversing hierarchy. Queries return
sum of each car's parts.

If you would like to see more specific data modelled in dbd, please
post (or email).

Hierarchy of Parts Modelled:
CivicLE
EngineCfg1
1.5L Engine
Starter1
Gear2
Manual Transmission
Gear1

CivicSE
EngineCfg2
1.6L Engine
Starter1
Gear2
Automatic Transmission
Gear1
(new 'cost)

(new 'starter1 'starter)
(create starter1 cost (val+ '100))

(new 'eng_1.5L 'engine)
(create eng_1.5L part starter1)
(create eng_1.5L cost (val+ '1000))

(new 'eng_1.6L 'engine)
(create eng_1.6L part starter1)
(create eng_1.6L cost (val+ '1300))

(new 'gear1 'gear)
(create gear1 cost (val+ '5))

(new 'gear2 'gear)
(create gear2 cost (val+ '6))

(new 'trans_manual 'transmission)
(create trans_manual part gear1)
(create trans_manual cost (val+ '500))

(new 'trans_auto 'transmission)
(create trans_auto part gear1)
(create trans_auto cost (val+ '700))

(new 'engCfg1 'engineConfig)
(create engCfg1 part eng_1.5L)
(create engCfg1 part gear2)

(new 'engCfg2 'engineConfig)
(create engCfg2 part eng_1.6L)
(create engCfg2 part gear2)

(new 'honda 'mfg)

(new 'civic_le 'civic 'car)
(create civic_le mfg honda)
(create civic_le part engCfg1)
(create civic_le part trans_manual)

(new 'civic_se 'civic 'car)
(create civic_se mfg honda)
(create civic_se part engCfg2)
(create civic_se part trans_auto)

(; Find parts of civic_le)
(; Returns, engCfg1, eng_1.5L, starter1, gear2, trans_manual, gear1)
(selectRel civic_le part *)

(; Find parts of civic_se)
(; Returns, engCfg2, eng_1.6L, starter1, gear2, trans_auto, gear1)
(selectRel civic_se part *)

(; Get sum of civic_le part costs)
(; Return 1611)
(sum (select (nodeElem (selectRel civic_le part *)) cost *))

(; Get sum of civic_se part costs)
(; Returns 2111)
(sum (select (nodeElem (selectRel civic_se part *)) cost *))
For additional examples where things are made of things, see:
www.dbfordummies.com/example/Ex117.asp
www.dbfordummies.com/example/Ex123.asp

[Neo]

www.kel-tec.com/su16aparts.html You might heard of that relational database

stores the whole rifle diassembled completely as this diagram suggests:
table Parts (partNo integer, ...);

Contrary to what object people may say that assembling and disassembling things within a computer environment is not a big deal. It is certainly not a reason to dismiss relational approach.

I don't dismiss the relational approach. It is appropriate for many
applications. However I would like to compare it with alternative
methods. Would anyone be willing to model the rifle in an rmdb and
compare it with dbd's solution? We can add varied properties to each
part (ie cost, materialType, weight) and run some queries.

[Aloha Kakuikanu]

Aloha Kakuikanu wrote:

table AssemblyParts (
assemblyPartNo integer, // informally it is a set#
partNo integer, // foreign key to Parts
);

Anybody noticed an implicit hierarchy of nested sets yet?

[Vadim Tropashko]

Anybody noticed an implicit hierarchy of nested sets yet?

Nested Sets
^^^^^^^^^^^^^^
Another approach to a tree structure is modeling it as nested sets

A
..|
...---- B
..|......|
..|.......------ C
..|......|
..|.......------ D
..|
...---- E

Figure 5.2a: A tree.

{ { [C] [D] } { [E] } }

Figure 5.2b: Nested sets structure for the tree at fig. 5.2a. Set
elements are boxes, and sets are the ovals including them. Every parent
set contains its children sets. (This is ASCII adaptation of the
figure, of course)
Clearly set containment can clearly accommodate any tree. Whenever we
need to grow a tree by adding a new child, we just nest one more set
into the appropriate parent set.

A naive nested sets implementation would materialize a set of elements
at each node. Aside from the fact that the RDBMS of your choice have
has to be capable of operating on sets on the datatype level , this
implementation would be quite inefficient. Every time a node is
inserted into a tree, the chain of all the containing sets should be
expanded to include (at least) one more element.

A more sophisticated variant of Nested Sets has been widely popularized
by Joe Celko. The main idea behind this encoding is representing nested
sets as intervals of integers...

[roma cox]

"Neo" <ne******@hotmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...

>www.kel-tec.com/su16aparts.html You might heard of that relational
database
stores the whole rifle diassembled completely as this diagram suggests:
table Parts (partNo integer, ...);

>Contrary to what object people may say that assembling and disassembling
things within a computer environment is not a big deal. It is certainly
not a reason to dismiss relational approach.

I don't dismiss the relational approach. It is appropriate for many
applications. However I would like to compare it with alternative
methods. Would anyone be willing to model the rifle in an rmdb and
compare it with dbd's solution? We can add varied properties to each
part (ie cost, materialType, weight) and run some queries.

Well, I promise nothing, but it was easy to get the rifle parts into a
table, and set up the relationship table, and I need the exercise.

David F. Cox

[Neo]

>www.kel-tec.com/su16aparts.html

compare it with alternative

Well, I promise nothing, but it was easy to get the rifle parts into a
table, and set up the relationship table, and I need the exercise.

In keeping with the OP problems, there should be parts which are made
of part recursively (like a Bill of Material). Is your data structure
similar to schema A or B?

Schema A:
Gun
part1
part2
part3
...
partN

Schema B:
Gun
part1
part23
part26
part2
part37
part58
part99
part33
part3
part67
...
partN

If it is similar to B, how did you determine it as the diagram is vague
in some cases. For example, do the group of parts located in the left
middle (240, 285, 236, 263, 205) consistute a sub part/assembly?

[Bob Badour]

Vadim Tropashko wrote:

>>Anybody noticed an implicit hierarchy of nested sets yet?

Nested Sets
^^^^^^^^^^^^^^
Another approach to a tree structure is modeling it as nested sets

A
.|
..---- B
.|......|
.|.......------ C
.|......|
.|.......------ D
.|
..---- E

Figure 5.2a: A tree.

{ { [C] [D] } { [E] } }

Figure 5.2b: Nested sets structure for the tree at fig. 5.2a. Set
elements are boxes, and sets are the ovals including them. Every parent
set contains its children sets. (This is ASCII adaptation of the
figure, of course)
Clearly set containment can clearly accommodate any tree. Whenever we
need to grow a tree by adding a new child, we just nest one more set
into the appropriate parent set.

A naive nested sets implementation would materialize a set of elements
at each node. Aside from the fact that the RDBMS of your choice have
has to be capable of operating on sets on the datatype level , this
implementation would be quite inefficient. Every time a node is
inserted into a tree, the chain of all the containing sets should be
expanded to include (at least) one more element.

A more sophisticated variant of Nested Sets has been widely popularized
by Joe Celko. The main idea behind this encoding is representing nested
sets as intervals of integers...

How does nested sets handle the part that is a part of multiple assemblies?

[Neo]

.. how did you determine [part/assembly structure] as the diagram is vague in some cases. For example, do the group of parts located in the left middle (240, 285, 236, 236, 283, 206) consistute a sub part/assembly?

If the OP can provide more data structure requirements and sample data,
I would rather focus on his application.

[NENASHI, Tegiri]

"Aloha Kakuikanu" <al*************@yahoo.comwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

Contrary to what object people may say that assembling and
disassembling things within a computer environment is not a big deal.
It is certainly not a reason to dismiss relational approach.

Now, there are some larger parts, ggregated from the smaller ones, for
example, trigger assembly, or stock. How do we handle these? Simple,
they are just sets:

table AssemblyParts (
assemblyPartNo integer, // informally it is a set#
partNo integer, // foreign key to Parts
);

Granted, some of the interested queries become set joins, so you have
to be familiar with the concept of set join. Other than that I don't
see any problems. Do you?

You want to say the set containment join, is not it ? If you do, there
are two problems: 1) the performance of set containment join; 2) the set
valued attribute, or relation valued attribute, is not realised by all
the databases. One can have a separate relation in the place of the
relation valued attribute, of course, but then how does one reference the
relation from AssemblyParts with assemblyPartNo ?

--
Tegi

>

[Aloha Kakuikanu]

NENASHI, Tegiri wrote:

"Aloha Kakuikanu" <al*************@yahoo.comwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

Contrary to what object people may say that assembling and
disassembling things within a computer environment is not a big deal.
It is certainly not a reason to dismiss relational approach.

Now, there are some larger parts, ggregated from the smaller ones, for
example, trigger assembly, or stock. How do we handle these? Simple,
they are just sets:

table AssemblyParts (
assemblyPartNo integer, // informally it is a set#
partNo integer, // foreign key to Parts
);

Granted, some of the interested queries become set joins, so you have
to be familiar with the concept of set join. Other than that I don't
see any problems. Do you?

You want to say the set containment join, is not it ? If you do, there
are two problems: 1) the performance of set containment join; 2) the set
valued attribute, or relation valued attribute, is not realised by all
the databases. One can have a separate relation in the place of the
relation valued attribute, of course, but then how does one reference the
relation from AssemblyParts with assemblyPartNo ?

No nested relations. Can't one express set containment join in plain
SQL the same way one writes relational division query? Next, how many
parts a typical vehicle has, pehaps 1000? Wouldn't relational division
kind of query perform OK on such relatively small dataset (no matter
how bad the execution plan is)?

[NENASHI, Tegiri]

"Aloha Kakuikanu" <al*************@yahoo.comwrote in
news:11*********************@e3g2000cwe.googlegrou ps.com:

>
NENASHI, Tegiri wrote:

>"Aloha Kakuikanu" <al*************@yahoo.comwrote in
news:11**********************@b28g2000cwb.googleg roups.com:

Contrary to what object people may say that assembling and
disassembling things within a computer environment is not a big
deal. It is certainly not a reason to dismiss relational approach.

Now, there are some larger parts, ggregated from the smaller ones,
for example, trigger assembly, or stock. How do we handle these?
Simple, they are just sets:

table AssemblyParts (
assemblyPartNo integer, // informally it is a set#
partNo integer, // foreign key to Parts
);

Granted, some of the interested queries become set joins, so you
have to be familiar with the concept of set join. Other than that I
don't see any problems. Do you?

You want to say the set containment join, is not it ? If you do,
there are two problems: 1) the performance of set containment join;
2) the set valued attribute, or relation valued attribute, is not
realised by all the databases. One can have a separate relation in
the place of the relation valued attribute, of course, but then how
does one reference the relation from AssemblyParts with
assemblyPartNo ?

No nested relations.

Then how you create the schema ? Please show with tables: what is it
that assemblyPartNo references ?

Can't one express set containment join in plain
SQL

One can but it is slow.

>the same way one writes relational division query? Next, how many
parts a typical vehicle has, pehaps 1000? Wouldn't relational division
kind of query perform OK on such relatively small dataset (no matter
how bad the execution plan is)?

It depends of the query: one can not really say without it.

>

[Aloha Kakuikanu]

NENASHI, Tegiri wrote:

"Aloha Kakuikanu" <al*************@yahoo.comwrote in
news:11*********************@e3g2000cwe.googlegrou ps.com:

NENASHI, Tegiri wrote:

You want to say the set containment join, is not it ? If you do,
there are two problems: 1) the performance of set containment join;
2) the set valued attribute, or relation valued attribute, is not
realised by all the databases. One can have a separate relation in
the place of the relation valued attribute, of course, but then how
does one reference the relation from AssemblyParts with
assemblyPartNo ?

No nested relations.

Then how you create the schema ? Please show with tables: what is it
that assemblyPartNo references ?

(referred to the rifle diagramm example above)

table Parts:
part# partName
------ -------------
100 - BARREL
102 - BARREL NUT
104 - BARREL EXTENSION
108 - BARREL RETAINER
110 - BOLT
112 - EXTRACTOR
114 - EXTRACTOR AXIS
116 - EJECTOR
....
194 - EXTRACTOR SPRING
....

table AssemblyParts:
assemblyPart# part# partName
----------------- ----- -------------
1 112 EXTRACTOR ASSEMBLY
1 114 EXTRACTOR ASSEMBLY
1 194 EXTRACTOR ASSEMBLY
1 112 BOLT ASSEMBLY
1 114 BOLT ASSEMBLY
1 194 BOLT ASSEMBLY
1 110 BOLT ASSEMBLY
1 116 BOLT ASSEMBLY

In this particular example we see that

{112,114,194} <= {112,114,194,110,116}

in other words, BOLT ASSEMBLY includes EXTRACTOR ASSEMBLY.

[Aloha Kakuikanu]

Aloha Kakuikanu wrote:

table AssemblyParts:
assemblyPart# part# partName
----------------- ----- -------------
1 112 EXTRACTOR ASSEMBLY
1 114 EXTRACTOR ASSEMBLY
1 194 EXTRACTOR ASSEMBLY
1 112 BOLT ASSEMBLY
1 114 BOLT ASSEMBLY
1 194 BOLT ASSEMBLY
1 110 BOLT ASSEMBLY
1 116 BOLT ASSEMBLY

Copy and paste typo. This has to be:

table AssemblyParts:
assemblyPart# part# *assembly*Name
----------------- ----- -------------
1 112 EXTRACTOR ASSEMBLY
1 114 EXTRACTOR ASSEMBLY
1 194 EXTRACTOR ASSEMBLY
*2* 112 BOLT ASSEMBLY
*2* 114 BOLT ASSEMBLY
*2* 194 BOLT ASSEMBLY
*2* 110 BOLT ASSEMBLY
*2* 116 BOLT ASSEMBLY

[Vadim Tropashko]

Aloha Kakuikanu wrote:

NENASHI, Tegiri wrote:

"Aloha Kakuikanu" <al*************@yahoo.comwrote in
news:11*********************@e3g2000cwe.googlegrou ps.com:

NENASHI, Tegiri wrote:
>You want to say the set containment join, is not it ? If you do,
>there are two problems: 1) the performance of set containment join;
>2) the set valued attribute, or relation valued attribute, is not
>realised by all the databases. One can have a separate relation in
>the place of the relation valued attribute, of course, but then how
>does one reference the relation from AssemblyParts with
>assemblyPartNo ?
>
No nested relations.

Then how you create the schema ? Please show with tables: what is it
that assemblyPartNo references ?

(referred to the rifle diagramm example above)

table Parts:
part# partName
------ -------------
100 - BARREL
102 - BARREL NUT
104 - BARREL EXTENSION
108 - BARREL RETAINER
110 - BOLT
112 - EXTRACTOR
114 - EXTRACTOR AXIS
116 - EJECTOR
...
194 - EXTRACTOR SPRING
...

table AssemblyParts:
assemblyPart# part# partName
----------------- ----- -------------
1 112 EXTRACTOR ASSEMBLY
1 114 EXTRACTOR ASSEMBLY
1 194 EXTRACTOR ASSEMBLY
1 112 BOLT ASSEMBLY
1 114 BOLT ASSEMBLY
1 194 BOLT ASSEMBLY
1 110 BOLT ASSEMBLY
1 116 BOLT ASSEMBLY

In this particular example we see that

{112,114,194} <= {112,114,194,110,116}

in other words, BOLT ASSEMBLY includes EXTRACTOR ASSEMBLY.

"Find all the AssemblyParts that include EXTRACTOR ASSEMBLY" - a
relational division query. It is essentially a hierarchical query
"Find all the nodes ancestors"
in this model.

Who might have think that the two favorite Celko's topics are so
closely related?

[Phil Stanton]

Sounds remarkably similar to a recipe DB that I wrote for a flavour making
company where a flavour (Car) was composed of (sub)Flavours(Engine,
Transmission) and ingredients(Nuts & bolts) Each SubFlavour (Engine) equally
comprised of SubSubFlavours(Fuel Pump) and ingredients (Nuts, bolts,
washers). This system could be bested indefinitely. In the end everything
was reduced to the basic ingredients. We then had details of all the
suppliers and their product details for each ingredient - i.e. Mssrs Smith
could supply 1" m6 screws reference 123 at £5.00 /1000 and Mssrs Jones could
supply 1" m6 screws reference ABC at £49p /100.
You could then have form that showed all the components that used 1" m6
screws.

Any help

Phil

"javelin" <go*************@spamgourmet.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

>I posted an answer to someone's question, and realized I have more
questions than answers. Thus, I am going to post my scenario to get to
the question that I have:

I have a challenge, to figure out what part of the vehicle to relate
parts to. I can't relate a part to the entire vehicle. Why? Well, a
vehicle can have, apparently, more than one engine configuration. For
example, I have a Honda Civic with a 1.5L engine, and it can come with
a 1.6L engine as well. Now, I don't know that much about auto
mechanics, but my understanding is that you can have some similar parts
for those two engines, and some different parts. Thus, I believe I need
to relate the parts to the engine configuration. However, that's only
for engine parts. There are also transmission parts. The car can come
in manual or automatic, so now you have different parts that relate to
the transmission on this particular vehicle. Going further (and getting
funner), your wheel base affects the parts you have, and then there's
the body style. Thus, you need to relate the part to the particular
application, not just the vehicle.

All this to ask if my thinking is correct: is it best to create an Xref
table between each autopart category (engine, transmission, exhaust,
body, etc) and the parts table, or one large xref table between the
two, with the addition of a category field. The one large xref table
would have it's foreign key related to each of the tables that govern
their group (e.g.: tbl_engine.engine_id,
tbl_transmission.transmission_id, etc)

Thanks in advance for the advice.

[Neo]

i.e. Mssrs Smith could supply 1" m6 screws reference 123 at £5.00 /1000and Mssrs Jones could supply 1" m6 screws reference ABC at £49p /100. You could then have form that showed all the components that used 1" m6 screws.

What is reference 123? How does it relate to the screw?

[NENASHI, Tegiri]

"Aloha Kakuikanu" <al*************@yahoo.comwrote in
news:11********************@k70g2000cwa.googlegrou ps.com:

>
NENASHI, Tegiri wrote:

>"Aloha Kakuikanu" <al*************@yahoo.comwrote in
news:11*********************@e3g2000cwe.googlegro ups.com:

NENASHI, Tegiri wrote:
You want to say the set containment join, is not it ? If you do,
there are two problems: 1) the performance of set containment join;
2) the set valued attribute, or relation valued attribute, is not
realised by all the databases. One can have a separate relation in
the place of the relation valued attribute, of course, but then how
does one reference the relation from AssemblyParts with
assemblyPartNo ?

No nested relations.

Then how you create the schema ? Please show with tables: what is it
that assemblyPartNo references ?

(referred to the rifle diagramm example above)

table Parts:
part# partName
------ -------------
100 - BARREL
102 - BARREL NUT
104 - BARREL EXTENSION
108 - BARREL RETAINER
110 - BOLT
112 - EXTRACTOR
114 - EXTRACTOR AXIS
116 - EJECTOR
...
194 - EXTRACTOR SPRING
...

table AssemblyParts:
assemblyPart# part# partName
----------------- ----- -------------
1 112 EXTRACTOR ASSEMBLY
1 114 EXTRACTOR ASSEMBLY
1 194 EXTRACTOR ASSEMBLY
1 112 BOLT ASSEMBLY
1 114 BOLT ASSEMBLY
1 194 BOLT ASSEMBLY
1 110 BOLT ASSEMBLY
1 116 BOLT ASSEMBLY

In this particular example we see that

{112,114,194} <= {112,114,194,110,116}

in other words, BOLT ASSEMBLY includes EXTRACTOR ASSEMBLY.

Then, it is not the set containment join that one can utilize but
perhaps the set containment division; but one has to know what queries
you suggest to execute to make it clear. Never the less, the
performance problem is still there even if it is a set containment
division.

>

[vc]

Vadim Tropashko wrote:

Aloha Kakuikanu wrote:

In this particular example we see that

{112,114,194} <= {112,114,194,110,116}

in other words, BOLT ASSEMBLY includes EXTRACTOR ASSEMBLY.

"Find all the AssemblyParts that include EXTRACTOR ASSEMBLY" - a
relational division query. It is essentially a hierarchical query
"Find all the nodes ancestors"
in this model.

Who might have think that the two favorite Celko's topics are so
closely related?

It is not surprising if you recall that that trees and powersets
ordered by inclusion are closely related by both being posets, or
alternatively a powerset ordered by inclusion is a tree.

[Phil Stanton]

The screws are to all intents and purposes identical and interchangeable.
"123" is Mssrs Jones reference for their version of a 1" m6 screw, while
another supplier, Mssrs Smith, use "ABC" as their reference no. for the same
specification screw. So the supplier's reference no.. You would probably
have a totally different reference maybe something like "Screw_1_M6" so just
different suppliers of Screw_1_M6

Hope that clarifies it
Phil

"Neo" <ne******@hotmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

i.e. Mssrs Smith could supply 1" m6 screws reference 123 at £5.00 /1000
and Mssrs Jones could supply 1" m6 screws reference ABC at £49p /100. You
could then have form that showed all the components that used 1" m6
screws.

What is reference 123? How does it relate to the screw?

[Neo]

>What is reference 123?

... supplier's reference no..

Ok, thanks.

Below dbd example models parts that make up a Civic LE and Civic SE.
Each has a different engine configuration and transmission however some
parts like starter and gears are shared. Each part can be supplied be
multiple suppliers. Each supplier has a different part# and cost.
Queries return all parts of each car. Queries return total cost of car
based on its parts. From Honda, one can drill down to supplier parts.

>From Suppliers, one can drill down to Honda parts. If anyone wants to

browse the populated db (fits on a floppy), email me.

CivicLE
EngineCfg1
1.5L Engine
starter1
carb1
Manual Transmission
gear1

CivicSE
EngineCfg2
1.6L Engine
starter1
carb2
Automatic Transmission
gear1
gear2
Following manufacturers supplier parts for above:
mfg1
gear1
gear2
carb1
starter1

mfg2
gear1
gear2
carb2
starter1
(new 'part#)
(new 'cost)

(new 'gear1 'gear)
(set gear1 part# (val+ 'hG1))
(set gear1 cost (val+ '10))

(new 'gear2 'gear)
(set gear2 part# (val+ 'hG2))
(set gear2 cost (val+ '20))
(new 'starter1 'starter)
(set starter1 part# (val+ 'hSt1))
(set starter1 cost (val+ '100))
(new 'carb1 'carburetor)
(set carb1 part# (val+ 'hC1))
(set carb1 cost (val+ '300))

(new 'carb2 'carburetor)
(set carb2 part# (val+ 'hC2))
(set carb2 cost (val+ '400))
(new 'eng_1.5L 'engine)
(set eng_1.5L part starter1)
(set eng_1.5L part carb1)

(new 'eng_1.6L 'engine)
(set eng_1.6L part starter1)
(set eng_1.6L part carb2)
(new 'engCfg1 'engineConfig)
(set engCfg1 part eng_1.5L)

(new 'engCfg2 'engineConfig)
(set engCfg2 part eng_1.6L)
(new 'trans_manual 'transmission)
(set trans_manual part# (val+ 'hTm))
(set trans_manual part gear1)

(new 'trans_auto 'transmission)
(set trans_auto part# (val+ 'hTa))
(set trans_auto part gear1)
(set trans_auto part gear2)
(new 'civic_le 'civic 'car)
(set civic_le part engCfg1)
(set civic_le part trans_manual)

(new 'civic_se 'civic 'car)
(set civic_se part engCfg2)
(set civic_se part trans_auto)
(new 'make 'verb)

(new 'honda 'mfg)
(set honda make civic_le)
(set honda make civic_se)
(new 'supplierFor 'verb)
(new 'supplier 'verb)
(set supplierFor reciprocal supplier)
(set supplier reciprocal supplierFor)
(new 'mfg1 'mfg)
(set mfg1 make (block (new 'gear1_m1 'gear)
(set (it) part# (val+ 'm1G1))
(set (it) cost (val+ '7))
(setWRR (it) supplierFor gear1)
(return (it))))
(set mfg1 make (block (new 'gear2_m1 'gear)
(set (it) part# (val+ 'm1G2))
(set (it) cost (val+ '17))
(setWRR (it) supplierFor gear2)
(return (it))))
(set mfg1 make (block (new 'starter1_m1 'starter)
(set (it) part# (val+ 'm1S))
(set (it) cost (val+ '70))
(setWRR (it) supplierFor starter1)
(return (it))))
(set mfg1 make (block (new 'carb1_m1 'carburetor)
(set (it) part# (val+ 'm1C1))
(set (it) cost (val+ '270))
(setWRR (it) supplierFor carb1)
(return (it))))

(new 'mfg2 'mfg)
(set mfg2 make (block (new 'gear1_m2 'gear)
(set (it) part# (val+ 'm2G1))
(set (it) cost (val+ '8))
(setWRR (it) supplierFor gear1)
(return (it))))
(set mfg2 make (block (new 'gear2_m2 'gear)
(set (it) part# (val+ 'm2G2))
(set (it) cost (val+ '18))
(setWRR (it) supplierFor gear2)
(return (it))))
(set mfg2 make (block (new 'starter1_m2 'starter)
(set (it) part# (val+ 'm2S))
(set (it) cost (val+ '80))
(setWRR (it) supplierFor starter1)
(return (it))))
(set mfg2 make (block (new 'carb2_m2 'carburetor)
(set (it) part# (val+ 'm2C2))
(set (it) cost (val+ '380))
(setWRR (it) supplierFor carb2)
(return (it))))
(; Get civic_le parts)
(; Gets engCfg1, eng_1.5L, starter1, carb1, trans_manual, gear1)
(getRel civic_le part *)

(; Get civic_se parts)
(; Gets engCfg2, eng_1.6L, starter1, carb2, trans_auto, gear1, gear2)
(getRel civic_se part *)
(; Get sum of civic_le part costs)
(; Return 410)
(sum (get (getElemLast (getRel civic_le part *)) cost *))
(; Get sum of civic_se part costs)
(; Returns 530)
(sum (get (getElemLast (getRel civic_se part *)) cost *))

[javelin]

OK, please STOP THE PRESSES! Well, let me restart this and say, thanks
for all the great responses. Unfortunately, I apparently screwed up by
mentioning parts of different "parts" of the vehicle. I meant different
"sections" of the vehicle, like the transmission, engine, exhaust
system, etc.

I do understand hierarchical table and application design (although I
don't know if I can spell it correctly).

Neo's example helps clarify a bit. In the example below, the starter1
and gear1 parts apply to different vehicles, which is easy to manage
via an XRef table. My concern is associating the part to the different
vehicle sections. In the example below, the starter1 (or 2 or 3, etc)
should relate to the engine. Thus, do I have to identify this in a
table, or manage it strictly via the program? The same applies to the
transmission section, to which the "gear1" part is related. How do I
say that the current user selection pertains to engines, transmissions,
etc, and only show related parts, or even limit entry to appropriate
parts???

Thanks again.

---------------------------

EXAMPLE:
CivicLE
EngineCfg1
1.5L Engine
starter1
carb1
Manual Transmission
gear1

CivicSE
EngineCfg2
1.6L Engine
starter1
carb2
Automatic Transmission
gear1
gear2

[Neo]

I apparently screwed up by mentioning parts of different "parts" of the vehicle. I meant different "sections" of the vehicle, like the transmission, engine, exhaust system, etc.

Could one think of sections as parts also. If so, you might consolidate
the hierarchy in just two core tables: T_Part and T_Par_Child

which is easy to manage via an XRef table.

What is a XRef table?

My concern is associating the part to the different vehicle sections. In the example below, the starter1 (or 2 or 3, etc) should relate to the engine. Thus, do I have to identify this in a table, or manage it strictly via the program?

I would opt to store the relationships in the db.

>The same applies to the transmission section, to which the "gear1" part is related. How do I say that the current user selection pertains to engines, transmissions, etc, and only show related parts, or even limit entry to appropriate parts???

Search for part (gear1) in T_Par_Child's child column. It should be in
two rows, where the Parent part is manual_trans and auto_trans. After
selecting one the trans (ie auto), find all rows with it in Par col,
and it should return child parts (ie gear1, gear2).

[javelin]

I suppose sections could be parts, but to assign a part number to a
section doesn't still doesn't solve the problem. How is that part then
related to the vehicle: by the engine, by the transmission, by
something else?

An Xref table is a cross reference table used for many to many
relationships. Many vehicles can relate to the same part. However,
again, what "section" of the vehicle is the part related to?

I definitely want to store relationships in the DB, but don't know what
those relationships are.

I'm still a bit confused by your T_Part and T_Par_Child analogy. I
think I need to go eat dinner and re-read it.

Thanks for the input. I really do appreciate it.
Neo wrote:

I apparently screwed up by mentioning parts of different "parts" of the vehicle. I meant different "sections" of the vehicle, like the transmission, engine, exhaust system, etc.

Could one think of sections as parts also. If so, you might consolidate
the hierarchy in just two core tables: T_Part and T_Par_Child

which is easy to manage via an XRef table.

What is a XRef table?

My concern is associating the part to the different vehicle sections. In the example below, the starter1 (or 2 or 3, etc) should relate to the engine. Thus, do I have to identify this in a table, or manage it strictly via the program?

I would opt to store the relationships in the db.

The same applies to the transmission section, to which the "gear1" part is related. How do I say that the current user selection pertains to engines, transmissions, etc, and only show related parts, or even limit entry to appropriate parts???

Search for part (gear1) in T_Par_Child's child column. It should be in
two rows, where the Parent part is manual_trans and auto_trans. After
selecting one the trans (ie auto), find all rows with it in Par col,
and it should return child parts (ie gear1, gear2).

[Jeff Smeker]

Maybe I'm missing something here, but it seems to me...

All things are parts (cars, sections, parts)
Any part can be a parent
Any part can be a child
Every part can have supplier information (i.e. multiple suppliers,
different supplier part numbers)

P1 - Small Car
P2 - Engine, Version 1
P3 - Starter
P4 - Bolt M6
...
P16 - Body Assembly
P27 - Some sheet metal part
P39 - Mid Size Car
P67 - Engine, Version 2
P3 - Starter
P92 - Bolt M10

Two primary tables are required for the above. Parts table, and a
linking table. An example of the linking table using the data above:

Parent_Part Child_Part
P1 P2
P1 P16
P39 P67
P2 P3
P3 P4
P16 P27
P67 P3
P3 P92


javelin wrote:

I suppose sections could be parts, but to assign a part number to a
section doesn't still doesn't solve the problem. How is that part then
related to the vehicle: by the engine, by the transmission, by
something else?

An Xref table is a cross reference table used for many to many
relationships. Many vehicles can relate to the same part. However,
again, what "section" of the vehicle is the part related to?

I definitely want to store relationships in the DB, but don't know what
those relationships are.

I'm still a bit confused by your T_Part and T_Par_Child analogy. I
think I need to go eat dinner and re-read it.

Thanks for the input. I really do appreciate it.
Neo wrote:

I apparently screwed up by mentioning parts of different "parts" of the vehicle. I meant different "sections" of the vehicle, like the transmission, engine, exhaust system, etc.

Could one think of sections as parts also. If so, you might consolidate
the hierarchy in just two core tables: T_Part and T_Par_Child

which is easy to manage via an XRef table.

What is a XRef table?

My concern is associating the part to the different vehicle sections. In the example below, the starter1 (or 2 or 3, etc) should relate to the engine. Thus, do I have to identify this in a table, or manage it strictly via the program?

I would opt to store the relationships in the db.

>The same applies to the transmission section, to which the "gear1" part is related. How do I say that the current user selection pertains to engines, transmissions, etc, and only show related parts, or even limit entry to appropriate parts???

Search for part (gear1) in T_Par_Child's child column. It should be in
two rows, where the Parent part is manual_trans and auto_trans. After
selecting one the trans (ie auto), find all rows with it in Par col,
and it should return child parts (ie gear1, gear2).

[javelin]

You may be right as far as purchases go: customers can buy the whole
car, the engine, the transmission, a single gear, etc. However,
whenever I've been to an autoparts store, they generally ask for
criteria that leads them to a part. The criteria is not considered a
part. I'm trying to figure out how they "group" the parts: logical
vehicle sections (engine, undercar, exhaust, transmission, body, frame,
etc), or what?
Jeff Smeker wrote:

Maybe I'm missing something here, but it seems to me...

All things are parts (cars, sections, parts)
Any part can be a parent
Any part can be a child
Every part can have supplier information (i.e. multiple suppliers,
different supplier part numbers)

P1 - Small Car
P2 - Engine, Version 1
P3 - Starter
P4 - Bolt M6
...
P16 - Body Assembly
P27 - Some sheet metal part
P39 - Mid Size Car
P67 - Engine, Version 2
P3 - Starter
P92 - Bolt M10

Two primary tables are required for the above. Parts table, and a
linking table. An example of the linking table using the data above:

Parent_Part Child_Part
P1 P2
P1 P16
P39 P67
P2 P3
P3 P4
P16 P27
P67 P3
P3 P92


javelin wrote:

I suppose sections could be parts, but to assign a part number to a
section doesn't still doesn't solve the problem. How is that part then
related to the vehicle: by the engine, by the transmission, by
something else?

An Xref table is a cross reference table used for many to many
relationships. Many vehicles can relate to the same part. However,
again, what "section" of the vehicle is the part related to?

I definitely want to store relationships in the DB, but don't know what
those relationships are.

I'm still a bit confused by your T_Part and T_Par_Child analogy. I
think I need to go eat dinner and re-read it.

Thanks for the input. I really do appreciate it.
Neo wrote:

I apparently screwed up by mentioning parts of different "parts" of the vehicle. I meant different "sections" of the vehicle, like the transmission, engine, exhaust system, etc.
>
Could one think of sections as parts also. If so, you might consolidate
the hierarchy in just two core tables: T_Part and T_Par_Child
>
which is easy to manage via an XRef table.
>
What is a XRef table?
>
My concern is associating the part to the different vehicle sections. In the example below, the starter1 (or 2 or 3, etc) should relate to the engine. Thus, do I have to identify this in a table, or manage it strictly via the program?
>
I would opt to store the relationships in the db.
>
The same applies to the transmission section, to which the "gear1" part is related. How do I say that the current user selection pertains to engines, transmissions, etc, and only show related parts, or even limit entry to appropriate parts???
>
Search for part (gear1) in T_Par_Child's child column. It should be in
two rows, where the Parent part is manual_trans and auto_trans. After
selecting one the trans (ie auto), find all rows with it in Par col,
and it should return child parts (ie gear1, gear2).

[-CELKO-]

>All things are parts (cars, sections, parts) <<
Yes

>Any part can be a parent <<

No, some parts are atomic

>Any part can be a child <<

No; there is a final assembly

>Every part can have supplier information (i.e. multiple suppliers,different supplier part

numbers) <<
Yes, but the intermediate assemblies are supplied by us from atomic
parts.

[Jeff Smeker]

-CELKO- wrote:

All things are parts (cars, sections, parts) <<

Yes

Any part can be a parent <<

No, some parts are atomic

I assume this means it is just a single part, with no children. If so,
I didn't mean that every part HAD to be a parent, just that any part
COULD be. From a DB structure point of view.

>

Any part can be a child <<

No; there is a final assembly

Again, a part does not HAVE to be a child, but any part COULD be.

>

Every part can have supplier information (i.e. multiple suppliers,different supplier part

numbers) <<
Yes, but the intermediate assemblies are supplied by us from atomic
parts.

Once again, this is fine. The part does not require supplier info, but
could, if needed.
I must be confused, I thought, per the OP:

>I have a challenge, to figure out what part of the vehicle to relate parts to.<

Well, the structure I presented does just that.

[javelin]

Jeff & Celko,

First of all, thanks for all of the input. I have to say I don't agree
that all sectios of the automobile are parts and more than the vehicle
itself is a part. Perhaps in real life, the consumer will but a 2000
Ford Escort, and later may purchase a head gasket for it, so to that
customer each one is simply a part# on his invoice.

In regards to my current database, I have a Vehicle table, an
EngineConfiguration table, a Transmission table, and a host of others.
I believe each vehicle (make, model, year, body style, etc) must have
different sections, and each section can have many parts. I know a part
can be used in multiple sections, so this calls for an Xref
(crossreference) table. I can only assume that this is the way your
typical autoparts store handles things, but I'm not sure. I may have to
post this on an autoparts site to get a better idea.

Thanks for the input, and any more ideas you may have.

Jeff Smeker wrote:

-CELKO- wrote:

>All things are parts (cars, sections, parts) <<

Yes

>Any part can be a parent <<

No, some parts are atomic

I assume this means it is just a single part, with no children. If so,
I didn't mean that every part HAD to be a parent, just that any part
COULD be. From a DB structure point of view.

>Any part can be a child <<

No; there is a final assembly

Again, a part does not HAVE to be a child, but any part COULD be.

>Every part can have supplier information (i.e. multiple suppliers,different supplier part

numbers) <<
Yes, but the intermediate assemblies are supplied by us from atomic
parts.

Once again, this is fine. The part does not require supplier info, but
could, if needed.
I must be confused, I thought, per the OP:

I have a challenge, to figure out what part of the vehicle to relate parts to.<

Well, the structure I presented does just that.

[jlepack]

Throwing in my $0.02 a little later than most, but when I read this
message I was thinking along the exact same lines a Jeff.

A part table - tracks the information about the part (name, item #
etc.)
Xref table - same as what jeff listed in his last post, that's perfect

but then you could have a field in the parts table called part type.
This could be linked to a part_type table that has part_type names and
descriptions and anything else about that type of part.

Some examples of part-types are "Engines", "Cars", "Screws", etc, etc,
etc.

javelin wrote:

Jeff & Celko,

First of all, thanks for all of the input. I have to say I don't agree
that all sectios of the automobile are parts and more than the vehicle
itself is a part. Perhaps in real life, the consumer will but a 2000
Ford Escort, and later may purchase a head gasket for it, so to that
customer each one is simply a part# on his invoice.

In regards to my current database, I have a Vehicle table, an
EngineConfiguration table, a Transmission table, and a host of others.
I believe each vehicle (make, model, year, body style, etc) must have
different sections, and each section can have many parts. I know a part
can be used in multiple sections, so this calls for an Xref
(crossreference) table. I can only assume that this is the way your
typical autoparts store handles things, but I'm not sure. I may have to
post this on an autoparts site to get a better idea.

Thanks for the input, and any more ideas you may have.

Jeff Smeker wrote:

-CELKO- wrote:

All things are parts (cars, sections, parts) <<
Yes
>
Any part can be a parent <<
No, some parts are atomic

I assume this means it is just a single part, with no children. If so,
I didn't mean that every part HAD to be a parent, just that any part
COULD be. From a DB structure point of view.

>
Any part can be a child <<
No; there is a final assembly

Again, a part does not HAVE to be a child, but any part COULD be.

>
Every part can have supplier information (i.e. multiple suppliers,different supplier part
numbers) <<
Yes, but the intermediate assemblies are supplied by us from atomic
parts.

Once again, this is fine. The part does not require supplier info, but
could, if needed.
I must be confused, I thought, per the OP:

>I have a challenge, to figure out what part of the vehicle to relate parts to.<

Well, the structure I presented does just that.

[Neo]

First of all, thanks for all of the input. I have to say I don't agree

that all sectios of the automobile are parts and more than the vehicle
itself is a part. Perhaps in real life, the consumer will but a 2000
Ford Escort, and later may purchase a head gasket for it, so to that
customer each one is simply a part# on his invoice.

While different, thinking of sections as parts, allows fewer tables.
You could thinks in terms of things. Some things are sections and some
things are parts (use attributes to distinguish). And these things have
a parent/child or BOM-type relationships.

In regards to my current database, I have a Vehicle table, an
EngineConfiguration table, a Transmission table, and a host of others.

At extremes, there are two type of solutions to your problem in RMDBs.
One solutions ends up with many specialized tables. The other solution
ends up with fewer generalized tables with recursive joins. Each has
advantages and disadvantages. You will have to find the right balance
for your specific requirements.

[Aloha Kakuikanu]

Neo wrote:

At extremes, there are two type of solutions to your problem in RMDBs.
One solutions ends up with many specialized tables. The other solution
ends up with fewer generalized tables with recursive joins. Each has
advantages and disadvantages. You will have to find the right balance
for your specific requirements.

And the third alternative is to use sets (where atomic parts are
elements, and the part assemblies are sets). No recursion is needed
then! The ancestor chain query is a relational division, but in a
typical autoparts shop application, why would one like to know what
chain of assemblies includes an atomic part?

BTW, it seems like your replies progressed from crankish to
knowledgeable -- congratulations! Gonna miss your db4d stuff. Well,
maybe not:-)

[Volker Hetzer]

Aloha Kakuikanu schrieb:

Neo wrote:

>At extremes, there are two type of solutions to your problem in RMDBs.
One solutions ends up with many specialized tables. The other solution
ends up with fewer generalized tables with recursive joins. Each has
advantages and disadvantages. You will have to find the right balance
for your specific requirements.

And the third alternative is to use sets (where atomic parts are
elements, and the part assemblies are sets). No recursion is needed
then! The ancestor chain query is a relational division, but in a
typical autoparts shop application, why would one like to know what
chain of assemblies includes an atomic part?

fiddly bits found in junk box -valve
lead, clamp -weight
valve, tyre, rim, weights -wheel
wheels, lots of other stuff -car
So, i'd prefer the recursion. That gives more flexibility with respect
to the level of detail.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.

[-CELKO-]

> I don't agree that all sectios of the automobile are parts and more than the vehicle itself is a part. Perhaps in real life, the consumer will but a 2000 Ford Escort, and later may purchase a head gasket for it, so to that customer each one is simply a part# on his invoice. <<

On the legal side your car IS the engine block with its VIN.
Everything else is a part added to it.