So I have fairly basic inventory app running now that works across the multiple locations we have. My next step for improvement is setting up some auto price updates and vendor comparisons. I believe I am going to have to rejig my data for the best approach to avoid lengthy and time consuming virtual columns. To simplify; the portions of my app that will need to change are about as follows.
I have PRODUCTS tab that records all the unique items we stock and various other tabs that record the ins and outs and the where toโs. Now I need to set up a multitude of vendors who all stock the items we do, but who all use a different sku and provide the items at different prices at different times. I had currently started setting up a table that has one column for our SKU, and then 3 columns for each Vendor (which could be reduced to two) as their SKU, their NAME, and their PRICE. I have second guessed this approach as now the columns will limit adding new Vendors. Am I better off making a separate table for every vendor? Or perhaps keep the vendors as I had previously as just a list in their own table and create one long table to tie every vendor to every sku with a separate price? My only fear on this method is that we currently have over 4000 SKUโs which would in turn have to link to 40+ vendors, which could be up to or over 1,600,000 rows of data for other tables and expressions to sort throughโฆ
Looking for advice on how to best optimize this and subsequent formulas (ie, price check all 40 vendors for this sku on order etc etc)
LinkedIn
Twitter
