Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002

    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.
    LEVEL1=any price
    LEVEL2=any price
    LEVEL3=any price
    LEVEL4=any price

    Example: item5 - sched20 - $10.00 - $20.00 - $30.00 - $40.00

    However, items can have multiple schedules associated with them!

    Example: item5 - sched20 - $10.00 - $20.00 - $30.00 - $40.00
    item5 - sched30 - $15.00 - $30.00 - $45.00 - $60.00

    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.

    Thanks, Tom

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you said "There has to be a better way to do this"

    sorry, there isn't

    you need a lot of different key combinations, and the best way of doing that is in different tables

    those tables may not all have the maximum number of possible rows, especially with all those override situations, but you'll have a couple of large tables for sure

    let me ask you a question

    are you sure you want to run a database of this size on mysql?

    if it's mainly retrieval, you might swing it, but it sounds like there will be a lot of price updating over time...


  3. #3
    Join Date
    Sep 2002
    really, really really baaaaad way of utilizing databases i think.. find out who thought of it and beat him up or something..

    force the use of a more mathematical system, in order to decrease the complexity..

    for example if you have 3 products of prices;


    say 'K' for store 1 is 10 and 'K' for store 2 is 12,

    let the prices be

    $10 & $12
    $20 & $24
    $30 & $36
    and you have now removed like... many records..

    if no one likes your idea explain it to them, ask them to do the thing themselves.. if they still dont like it threaten them, if they still dont like it resign.. or use the old system..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts