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

On Mar 6, 6:51 am, Rik <luiheidsgoe...@hotmail.com> wrote:
> 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

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 <zac.carey@gmail.com> wrote:
> On Mar 6, 6:51 am, Rik <luiheidsgoe...@hotmail.com> wrote:
>> 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 asan
>> 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.
>
> 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

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 wrote:
> strawberry <zac.carey@gmail.com> wrote:
>> On Mar 6, 6:51 am, Rik <luiheidsgoe...@hotmail.com> wrote:
>>> 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.
>>
>> 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
>
> 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

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.
jstucklex@attglobal.net
==================

>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.

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 Burditt <gordonb.pvcz0@burditt.org> wrote:

>> 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.
>
> 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?

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