Problem We have a number of different customers getting a number of different price files, each customer can then also have different styles of pricing files for example customers can have just two columns, product and price. Some customers can have up to 20-30 fields each of these fields can exist in different tables in the database. Also the number of products within the file can vary also some with only a couple of hundred some with over 10k. These files have to then be run every hour to keep stock and pricing current. On average theres about 50 different file formats for 180 pricefiles so in total its 9000 odd files to be written.
Current solution My current solution for this is two tables. One table with all the products within each of the price files along with there prices in various currency's. And another table/view with all the possible product attributes. Primary key is the product code, then other fields for example short description, long description, alternative product codes etc etc.
The program then loads both these tables in to memory:
ConcurrentDictionary<PRODUCT_CODE, ConcurrentDictionary<COLUMN_NAME, VALUE>>
I then have the pricefile definition table. The table then details the price file format for eample, price file x has column '
Product_Code', 'Price_GBP', 'Description' the program then iterates (parallel) through the products within the current pricefile and grabs the columns from the concurrent dictionary's in memory. As well as columns in the dictionary there are all 'Programmable' columns for example
'RRP - price_gbp *1.4' These just go off to a switch case to see what it needs to do with these columns.
Issue My issue is that the number of product attributes is becoming very large and the number of price files and price file formats and customers wanting these files has almost 10 folded in the last couple of years since i write this system. So as you can imagine its run time is no where as quick as it was.
I cant think of anyway of making this quicker, neater and 'future proof'.