Re: vehicle to autoparts relationships

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? Received on Wed Nov 22 2006 - 21:23:02 CET