Unanswered: If you can solve this you are a GOD!!!
I have been struggling with this design question for about 2 weeks now, and would appreciate any help.
O.K. Here goes. I will try and explain this in as much detail as possible.
SCENERIO: I currently administer a database that stores my customers product and pricing information (along with a lot of other stuff). The database, as it stands now is just a bunch of "quick fixes" one on top of the other. A.K.A TOTALLY MESSED UP!! I am currently working on the redesign, but have been hung up on this pricing issue for quite some time. The ITEMS table that stores the information for each given product is fine, but the part that I am struggling with is how to properly link each product to its respective price. Sound's simple enough, right? Wait, it get's better!!
THE DATA: Our customers consist of warehouses, and dealers, linked by a 1 to MANY relationship. The warehouses determine what products can be displayed to any given dealer. It may be items 1,2, and 3 for dealer A, and 2,4, and 6 for dealer B (you get the picture). The same item can have a different price for each dealer. Item 2 for dealer A may be $5.00, while the same item, Item 2 for dealer B is $10.00. Wait it get's better!! We have the following schedule information from the warehouses to decide pricing for dealers:
ITEM#=the item number of the given product
SCHEDULE=the name of the pricing schedule for this item.
With 50,000 items, if each warehouse only specified 1 schedule for each item we would only have 50,000 schedules. However, the average warehouse uses about 10 (some use 20) schedules (you'll see why in a minute). Thus taking our total up to 500,000 schedules. Assuming no future growth, multiply that number times 50 warehouses!! 25,000,000 records in that one table. By adding just one more schedule making 11 for each warehouse you would increase the final record count to 27,500,000. We haven't even stored the actual prices yet!! Ideally, any given warehouse should be able to have as many schedules as they need.
All this does is provide us the information we can use to determine the actual price that a dealer sees for a given item. The warehouse then says, O.K., dealer A uses sched20 level3 for item5 or $30.00, while dealer B uses sched30 level3 or $45.00. However, this is not all. The warehouse can then say, dealer A is a good dealer, this month they still use sched20 level3 for item5 but with an additional 25% off or $22.50!! So to track which dealers get which prices requires another table of identical size to the table described above(25,000,000 records).
To add insult to injury, the above situation determines the dealers COST of a particular item. Dealer A can then come along and say, This item cost me $30.00 (or $22.50, depending on the discount percentage or lack there of). I need to turn around and sell it for a profit so mark up the price 45%. Then dealer B comes along and says. I paid $45.00 for the same item, but no one will pay more than $50.00 for it so I can only mark it up 11% OR MAYBE NOT AT ALL.
So now for any given item we have a cost price, a possible discount percentage, as well as a selling price, and possible mark up percentage. Each one of these price records could be different for each individual dealer, so realistically we could have 50 warehouses * 200 dealers * 50,000 items = 500,000,000 price records!!!
There has to be a better way to do this. I hope that I have provided ample information for you to chew on. If you have any questions please feel free to ask. I will try and explain as best I can. If any one has any good ideas on how to design a database to handle such a conundrum, please let me know, any responses will be greatly appreciated.