June 2, 2016 at 3:37 pm
What is the best way to design the following situation:
CompanyA purchased CompanyB and there needs to be a cross reference table (junction table) that matches the CompanyA products to CompanyB products
CompanyAProducts table
ItemNumber, ItemDescription
Example data
ItemNumber, ItemDescription, Company
W100 Wrench A
H100 Hammer A
F100 Flashlight A
CompanyBProducts table
ItemNumber, ItemDescription
Example data
ItemNumber, ItemDescription, Company
1111W Wrench B
2222H Hammer B
4444D Drill B
There is a junction table that cross references the items
ProdXref table
ItemNumberA, ItemNumberB
W100 1111W
H100 2222H
Now, I need to get a listing of all competitors items and create a cross reference to the competitors
Competitor Table
ItemNumber, ItemDescription, Company
WR1 Wrench Lowes
1234 Wrench Ace
HAM123 Hammer HomeDepot
444DRILL Drill Lowes
FLASH99 FlashLight Ace
The goal would be to see the following in the final output:
ItemNumber ItemDescription Company ItemNumber ItemDescription Company ItemNumber ItemDescription Company
WR1 Wrench Lowes 1111W Wrench B W100 Wrench A
1234 Wrench Ace 1111W Wrench B W100 Wrench A
HAM123 HammerHomeDepot 2222H Hammer B H100 Hammer A
444DRILL Drill Lowes 4444D Drill B
FLASH99 FlashLight Ace F100 FlashlightA
( see attachment for data layout)
What is the correct way to create another cross reference table that will handle the Flash light and Drill that do not exist in the original cross reference table?
Or is there a better way to model this data
June 3, 2016 at 10:28 am
Why don't you add the UPC code to the database and use that to link the various records together?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 3, 2016 at 10:48 am
Why not have a single Product table, and have the Company A and Company B tables map to it? Then competitor products could map to it as well. And so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2016 at 11:45 am
With just a quick look, I'd do something like below. I tried to follow your naming style, naturally change as needed.
Since we can't guarantee unique ProductNumbers across companies, we'll need to add CompanyCode to the key (it seemed inconsistent to me that the table name was "Products" and the column name was "ItemNumber", it should be consistent).
Tables: Companies; Products; ProductJunctions
Companies :: Keyed by ( CompanyCode )
CompanyCode
CompanyName
Products :: Keyed by ( CompanyCode, ProductNumber )
CompanyCode
ProductNumber
ProductDescription
ProductJunctions :: Keyed by ( CompanyCode1, ProductNumber1, CompanyCode2, ProductNumber2 )
--Lower company code is alway Code1, so that duplicate entries are not created.
--[Btw, no, I personally would not create an artificial key here, I prefer natural keys where possible.]
CompanyCode1
ProductNumber1
CompanyCode2
ProductNumber2
DateEntered
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply