Assuming I've done that, how do I get to searching by part number? Partial key match?


--
"Language shapes the way we think, and determines what we can think about."
-- B. L. Whorf

I think what you are searching for is database design or more specific database normalization.

Really you should probably put the product serial, price, anything else specific to that vendors offer on the product into a seperate table. Lets name it vendoroffer. Then have another table with the vendor, product, and vendoroffer keys having all your "cross-references" but none of the data. This is an example of normalization.

By keys I mean internally created numbers (i.e. autoincrement integers) that only have meaning in the database and not something like serial numbers.

I think what you are searching for is database design or more specific database normalization.

I think you're right. The common advice so far seems to be "put it in a database, then..." I'm OK with that, but I'm still groping in the dark a bit - not seeing my way to a solution yet - although what you suggest certainly seems like a step in the right direction.

I have to say that "normalization" seems to be the key here. I've heard it as a term of the art before, but I'm completely ignorant of how it's done (except for having done stuff like this myself, albeit on a smaller scale and without much thinking about it.)

I'm guessing that you mean something like this:

v_id    vendor
001     NGK
002     DENSO
003     BECK-ARNLEY
004     AUTOLITE
[download]

Then

id       v_id    plug
00001    001     ABC095
00002    001     XYZ096
....
00123    002     ZZZ000
....
04567    003     AAABBB999
34291    004     FOO-12
[download]

And then... I'm not sure what happens then. I thought I had it, but I've lost it. :( Help?


--
"Language shapes the way we think, and determines what we can think about."
-- B. L. Whorf

What you have done there is put your database in second normal form (2NF) sort of.

The bottom table of yours is the vendoroffer table I suggested earlier. It links together vendors and the actual tangible plug. This is the relationship of vendors to plugs and in db terms it is a many to many relationship. A plug can have many vendors and a vendor offers many plugs.

In this relationship vendors assign item numbers to the plugs they offer but these are unique to the vendor and should not be included with the actual plug's data.

Now you can have a third table plugs or parts, or whatever, with it's own internal key. In each row you can keep data specific to the actual plug, not specific to any vendor or any cross reference. Maybe what its dimensions are, its amperage, its quality?

Any more relationships to plugs are added to the plugs table with a foreign key to another table. Maybe a manufacturer or a car model. Unless, of course, it is a many-to-many relationship like what happened earlier.

Example con Arte de Ascii! (each box is a table, each entry a column)
PK = Primary Key -- FK = Foreign key
can you tell why?

vendor                 
-------------------    
| id [PK] integer |----------\
| name    string  |          |
-------------------          |
                             |
vendoroffer                  |
-----------------------      |
| vendor [FK] integer |<-----/
| plug   [FK] integer |<-----\
| serial      string  |      |
| price       decimal |      |
-----------------------      |
                             |
plug                         |
----------------------       |
| id    [PK] integer |-------/
| motor [FK] integer |----------------------> ...
| gap        float   |
| ...etc?...         |
----------------------
[download]

You can find lots of stuff on database normalization and design around the web. about.com's Normalization Basics seems pretty gentle.