Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: Organizing and presenting a cross-reference

by dragonchild (Archbishop)
on Sep 24, 2008 at 01:48 UTC ( #713333=note: print w/ replies, xml ) Need Help??


in reply to Organizing and presenting a cross-reference

You would have a products table and a vendors table. You would then have a product_x_vendor table which would be the xref. If each vendor had their own unique part number for a given product, the part number would go in the xref table.


My criteria for good software:
  1. Does it work?
  2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?


Comment on Re: Organizing and presenting a cross-reference
Re^2: Organizing and presenting a cross-reference
by oko1 (Deacon) on Sep 24, 2008 at 02:33 UTC

    Unfortunately, they're not unique - in fact, many vendors use the same number for a given part. I've seen the scheme you describe before (believe it or not, I was a database admin for a couple of years...), but I couldn't figure out how to make it work here.


    --
    "Language shapes the way we think, and determines what we can think about."
    -- B. L. Whorf
      IANADBA, but perhaps you should store all the values with their vendor-label prefix. Then at least you will eliminate duplicate part numbers for different parts.
      Keep the vendor name and part num separate (cols), but define a compound key on the table (vendor,part_num).

        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

        Then

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

        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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://713333]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (9)
As of 2014-10-24 10:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (131 votes), past polls