Re: vehicle to autoparts relationships

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).
Received on Wed Nov 29 2006 - 02:18:17 CET