in reply to Re^2: Organizing and presenting a cross-reference
in thread Organizing and presenting a cross-reference

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.

  • Comment on Re^3: Organizing and presenting a cross-reference

Replies are listed 'Best First'.
Re^4: Organizing and presenting a cross-reference
by oko1 (Deacon) on Sep 24, 2008 at 13:47 UTC
    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
[reply]
[d/l]
[select]

      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]

        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]

In Section Seekers of Perl Wisdom