Professional Web Applications Themes

Order system: limited supply, overview of orders currently (un)suppliable. - MySQL

Hi guys, I'm wondering wether I can solve this with pure SQL statements: I've got a shop system, with product-table, orders-table, an order_products table. The goal was to have a stock system implemented in this, with a quick overview of which orders currently can not be fullfilled due to lack of stock. The problem therein is when an item is limited on stock, some orders can be deliverd, others wil have to wait untill a new delivery. The logic to be used is: first ordered, first in line for the product. (Personally, as this is all 'pre-paid', I'd say: first ...

  1. #1

    Default Order system: limited supply, overview of orders currently (un)suppliable.

    Hi guys,

    I'm wondering wether I can solve this with pure SQL statements: I've got a
    shop system, with product-table, orders-table, an order_products table.
    The goal was to have a stock system implemented in this, with a quick
    overview of which orders currently can not be fullfilled due to lack of
    stock. The problem therein is when an item is limited on stock, some
    orders can be deliverd, others wil have to wait untill a new delivery. The
    logic to be used is: first ordered, first in line for the product.
    (Personally, as this is all 'pre-paid', I'd say: first paid, first in
    line, but hey).

    Tables:
    PRODUCTS
    product_id int(15)
    stock int(10)

    ORDERS
    order_id int(15)
    order_time datetime

    ORDER_PRODUCTS
    order_id int(15)
    product_id int(15)
    amount int(10) (pfff, as if we're going to get an order for a miljon..)

    'stock' is altered the moment an order is sent/packaged btw. Finding all
    orders having products that are low in stock atm is not really the
    problem, it's the orders that can be fullfilled with the current low stock
    an which can't...

    I'm solving it now using PHP and looping over orders, but it's a becoming
    a bit cludgy. Any ideas how to approach this? I've thought about a
    'reserved' field in the products table which get's altered as soon as an
    order is entered, and a flag (or maybe amount) in the ORDER_PRODUCTS table
    indicating wether it's suplliable with the current (stock - reservations),
    but again, it's a bit hard to maintain in a reliable manner with orders
    being cancelled, altered, etc.

    --
    Rik Wasmus
    Posted on Usenet, not any forum you might see this in.
    Ask Smart Questions: http://tinyurl.com/anel
    Rik Guest

  2. #2

    Default Re: Order system: limited supply, overview of orders currently (un)suppliable.

    On Mar 6, 6:51 am, Rik <com> wrote: 

    Wouldn't something like this work (untested, and perhaps
    misunderstood)...

    SELECT op.*,p1.product_id fulfillable, p2.product_id unfulfillable
    FROM `order_products` op
    LEFT JOIN products p1 ON p1.product_id = op.product_id
    AND p1.quantity <= op.quantity
    LEFT JOIN products p2 ON p2.poduct_id = op.product_id
    AND p2.quantity > op.quantity

    strawberry Guest

  3. #3

    Default Re: Order system: limited supply, overview of orders currently (un)suppliable.

    strawberry <com> wrote: 
    >
    > Wouldn't something like this work (untested, and perhaps
    > misunderstood)...
    >
    > SELECT op.*,p1.product_id fulfillable, p2.product_id unfulfillable
    > FROM `order_products` op
    > LEFT JOIN products p1 ON p1.product_id = op.product_id
    > AND p1.quantity <= op.quantity
    > LEFT JOIN products p2 ON p2.poduct_id = op.product_id
    > AND p2.quantity > op.quantity[/ref]

    Doesn't seem like it (seems to me either the <= & the > or
    'fullfillable'/unfullfillable should be the other way around on a side
    note), I'll test in a moment.
    The problem is this:
    1. 10 pieces of a particular product are in stock.
    2. Someone orders 8 of them.
    3. Someone orders 6 of them.

    Untill the first order is packaged & shipped, stock will be 10. Now, I'm
    looking for a query which will tell me the order at 2 can be fulfilled,
    but the order at 3 cannot.
    --
    Rik Wasmus
    Posted on Usenet, not any forum you might see this in.
    Ask Smart Questions: http://tinyurl.com/anel
    Rik Guest

  4. #4

    Default Re: Order system: limited supply, overview of orders currently (un)suppliable.

    Rik wrote: 
    >>
    >> Wouldn't something like this work (untested, and perhaps
    >> misunderstood)...
    >>
    >> SELECT op.*,p1.product_id fulfillable, p2.product_id unfulfillable
    >> FROM `order_products` op
    >> LEFT JOIN products p1 ON p1.product_id = op.product_id
    >> AND p1.quantity <= op.quantity
    >> LEFT JOIN products p2 ON p2.poduct_id = op.product_id
    >> AND p2.quantity > op.quantity[/ref]
    >
    > Doesn't seem like it (seems to me either the <= & the > or
    > 'fullfillable'/unfullfillable should be the other way around on a side
    > note), I'll test in a moment.
    > The problem is this:
    > 1. 10 pieces of a particular product are in stock.
    > 2. Someone orders 8 of them.
    > 3. Someone orders 6 of them.
    >
    > Untill the first order is packaged & shipped, stock will be 10. Now, I'm
    > looking for a query which will tell me the order at 2 can be fulfilled,
    > but the order at 3 cannot.
    > --Rik Wasmus
    > Posted on Usenet, not any forum you might see this in.
    > Ask Smart Questions: http://tinyurl.com/anel[/ref]

    Hi, Rik,

    Hmmm, I haven't thought this through much - but what if you create a
    temporary table with a column for left_in_stock. Start it out with a
    dummy order containing the current stock, then insert rows with the
    number of items ordered subtracted from the current stock.

    Alternatively, a SP which starts with the current stock and subtracts
    the ordered amount from stock, returning those with negative stock balances.

    It could be done with recursive SQL also, but that isn't supported yet.

    BTW - last time I needed to do this I ended up just doing it in the
    native language (C in this case). It actually was easier than trying to
    maintain understandable SQL.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Order system: limited supply, overview of orders currently (un)suppliable.

    >The problem is this: 

    How do you deal with this?

    1. 10 Catapults, 10 Dog Bones and 10 Mouse Balls are in stock.
    2. Someone orders 8 Catapults and 12 Mouse Balls.
    3. Someone orders 12 Dog Bones and 8 Catapults.
    4. Someone orders 8 Dog Bones and 8 Mouse Balls.
    5. Someone orders 3 Mouse Balls, 3 Dog Bones, and 3 Catapults.

    Given that you do not ship partial orders, how do you determine
    that you need to ship #4 now, and the other orders have to wait?
    Or do you decide to ship #5 and let the others wait?
    Gordon Guest

  6. #6

    Default Re: Order system: limited supply, overview of orders currently (un)suppliable.

    Gordon Burditt <org> wrote:
     
    >
    > How do you deal with this?
    >
    > 1. 10 Catapults, 10 Dog Bones and 10 Mouse Balls are in stock.
    > 2. Someone orders 8 Catapults and 12 Mouse Balls.
    > 3. Someone orders 12 Dog Bones and 8 Catapults.
    > 4. Someone orders 8 Dog Bones and 8 Mouse Balls.
    > 5. Someone orders 3 Mouse Balls, 3 Dog Bones, and 3 Catapults.
    >
    > Given that you do not ship partial orders, how do you determine
    > that you need to ship #4 now, and the other orders have to wait?
    > Or do you decide to ship #5 and let the others wait?[/ref]

    Very good point. They've formulated it as 'first ordered, first served'.
    Might not be the ideal solution indeed (allthough easiest to implement).
    I'll ask them formulate a more detailed policy.

    --
    Rik Wasmus
    Posted on Usenet, not any forum you might see this in.
    Ask Smart Questions: http://tinyurl.com/anel
    Rik Guest

Similar Threads

  1. Shockwave crashes system in limited user account
    By swsquish in forum Macromedia Shockwave
    Replies: 1
    Last Post: January 31st, 02:16 AM
  2. #40597 [NEW]: FILTER_VALIDATE_INT limited to system's signed integer size
    By mail957253 at lemurtastic dot com in forum PHP Bugs
    Replies: 1
    Last Post: February 22nd, 10:48 PM
  3. Event orders
    By Tumurbaatar S. in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: November 11th, 10:38 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139