vehicle to autoparts relationships
javelin
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
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" <google....@spamgourmet.com> wrote in message
news:1164221611.2...@b28g2000cwb.googlegroups.com...
Parts can have parts. Read up on "parts explosion".
Neo
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
> 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
> 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
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
news:1164230415....@m73g2000cwd.googlegroups.com...
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
> >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
How does nested sets handle the part that is a part of multiple assemblies?
Vadim Tropashko
OK, parts A and B are in the assembly E and parts B and C are in the
assembly F. Formally,
{A,B} = E
{B,C} = D
Admittedly, calling such sets "nested" is a stretch of terminology.
Moreover, this would make it impossible to represent such parts as
nested intervals (aka nested sets in Celko's terminology). Nested
intervals work for trees only.
Neo
If the OP can provide more data structure requirements and sample data,
I would rather focus on his application.
NENASHI, Tegiri
news:1164226982.3...@b28g2000cwb.googlegroups.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
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
>
> NENASHI, Tegiri wrote:
>> "Aloha Kakuikanu" <aloha.k...@yahoo.com> wrote in
>> news:1164226982.3...@b28g2000cwb.googlegroups.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:
> >> 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
> 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
"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
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" <google....@spamgourmet.com> wrote in message
news:1164221611.2...@b28g2000cwb.googlegroups.com...
Neo
What is reference 123? How does it relate to the screw?
NENASHI, Tegiri
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
"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" <neo5...@hotmail.com> wrote in message
news:1164305247.9...@e3g2000cwe.googlegroups.com...
Bob Badour
Hi Phil,
That's called a bill of materials (BOM), and it's a very common application.
Neo
> ... 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 *))
-CELKO-
The tree structure is in one table which has nodes like this:
CREATE TABLE Assemblies
(part_nbr INTEGER NOT NULL
REFERENCES Inventory(part_nbr)
ON UPDATE CASCADE,
part_qty INTEGER NOT NULL
CHECK (part_qty > 0)
ft INTEGER NOT NULL,
rgt NTEGER NOT NULL,
PRIMARY KEY (lft, rgt)
<< constraints >>);
The Inventory is keyed on part_nbr.
javelin
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:
Neo
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
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.
Jeff Smeker
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
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?
-CELKO-
>> 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
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.
jlepack
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.
Neo
> 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
> 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
> 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?
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.
Bob Badour
Like hell they did.
-CELKO-
On the legal side your car IS the engine block with its VIN.
Everything else is a part added to it.
paul c
Maybe you're right, but I thought there were two numbers for vehicles,
in North America anyway, and the number on the block is not the VIN. I
know that motorcycles always have two numbers, a frame number which is
treated, legally, as a VIN and used for registration purposes, but there
is also an engine number. I know first-hand that both are checked at
the border by the US Export Control people and if you can't show title,
they won't let it out of the country (even if it is the many-years-old,
cheap stuff I buy!).
Just curious, does anybody else know?
p
paul c
One big laugh in all this stuff is that some participants seem to think
they are talking about an natural kind of actuality whereas the
historical truth is that many of the relationships are unnatural and
were determined twenty or thirty years in a very artifical way, mostly
using codes dreamed up by people who thought hierarchical or network
databases were the be all and end all. Today every time I go to a
retail checkout counter and encounter a cashier who can't make change,
I'm reminded how much the false use of computers is endemic and what a
false reality it creates. Reminds me how fresh vegetables used to be
graded by the Canucks, maybe still are, had to do with a taste test by
the agriculture inspector and whether the importer got credited for
Fancy, Choice or Standard quality was mostly determined by how much the
inspector had to drink the previous evening. Not to tout the Yanks, but
to their credit they used a machine - it was called something like the
"Tenderometer".
p