How to convert upper diagonal of array into normalized tables
I have a spreadsheet with a lookup table enabling conversion of product id codes from one supplier to another. It is currently in the form of a kind of upper diagonal array like this. Rows come as pairs allowing translation from one supplier to another supplier1 supplier 2 codes, supplier 3 codes, supplier 4 codes,...supplier n codes. supplier1 supplier 1 codes, supplier 1 codes, supplier 1 codes...... supplier2 supplier 3 codes, supplier 4 codes,....supplier n codes supplier2 supplier 2 codes, supplier 2 codes...... supplier3 supplier 4 codes,.... supplier n codes supplier3 supplier 3 codes...... It is quite big with a couple of million supplier codes in the top line and ~ 30 suppliers and the array is not complete - not all suppliers supply all products. It has been suggested to convert this into normalized database tables. It is not clear to me the best strategy to do this and whether it is the best thing to do. You could take each row pair and convert it to a table with e.g supplier1 as the PK and FK to all other suppliers and generate ~ 30 tables. Are there better strategies?
Product table with productID that is your internal identifier, and is unique to that product. Supplier table with supplierID that is unique to supplier. SupplierProduct (or better named) table with the id of the supplier and productID of everything they supply, with another column for supplierProductID = their identifier for that product. Probably with dates attached so when they start/stop offering that product you just update that row, and search for today's date between the dates to find current inventory available. Gives you your own product list without duplicates, allows you to crosswalk to their identifier when you need to.