Re: Organizing and presenting a cross-reference

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:

Does it work?
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

Replies are listed 'Best First'.
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	[reply]
Re^3: Organizing and presenting a cross-reference by chrism01 (Friar) on Sep 24, 2008 at 05:43 UTC
Keep the vendor name and part num separate (cols), but define a compound key on the table (vendor,part_num).	[reply]
Re^4: Organizing and presenting a cross-reference by oko1 (Deacon) on Sep 24, 2008 at 13:56 UTC
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	[reply] [d/l]
Re^3: Organizing and presenting a cross-reference by juster (Friar) on Sep 24, 2008 at 08:57 UTC
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.	[reply]
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]
Re^5: Organizing and presenting a cross-reference by juster (Friar) on Sep 25, 2008 at 03:51 UTC
Re^6: Organizing and presenting a cross-reference by oko1 (Deacon) on Sep 25, 2008 at 22:02 UTC
Re^3: Organizing and presenting a cross-reference by wrinkles (Pilgrim) on Sep 24, 2008 at 03:22 UTC
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.	[reply]

In Section Seekers of Perl Wisdom