Re^4: Organizing and presenting a cross-reference

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

Comment on Re^4: Organizing and presenting a cross-reference Select or Download Code

Replies are listed 'Best First'.
Re^5: Organizing and presenting a cross-reference by juster (Friar) on Sep 25, 2008 at 03:51 UTC
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.	[reply] [d/l]
Re^6: Organizing and presenting a cross-reference by oko1 (Deacon) on Sep 25, 2008 at 22:02 UTC
Thank you very much - really appreciated! Again, I've done this kind of thing on a much smaller scale, but I've never run into anything large-scale, or even realized that there was a formalized method of dealing with this kind of problems (it seems odd on reflection, for someone who has been working with computers and programming for more than 25 years.) I'll take a look at that recommended URL as well as other resources. -- "Language shapes the way we think, and determines what we can think about." -- B. L. Whorf	[reply]