Rails - Jul 2006 - Store/product stock design question

Hi all,
I''m having trouble figuring out how to handle stock levels in a new
store I''m building. When products are entered into the admin of the
store, they have a stock number associated with them.

What I''m not sure about, is how I go about maintaining this stock
level. Take this scenario:

1. User A adds an item to their cart, and I check that it''s in stock -
all good.
2. User A continues to shop
3. User B adds the same item to their cart, and I check the stock
again - fine again.
4. User B checks out, pays for the item, and I decrement the stock
level for that product
5. User A is out of luck because someone has bought what''s in their
cart

How do you handle this situation?

Many thanks for any advice.

Cheers,
Jord

You could
* Tell customer B that unforuntately the products have been sold
* Dynamically determine the stock levels based on: #items unsold - 
#items in carts, so to protect the innocent.

Cheers
Nic

-- 
Posted via http://www.ruby-forum.com/.

> * Tell customer B that unforuntately the products have been sold
Do you mean customer A? So, they bought something and then lost out
because customer B bought it in the meantime?
> * Dynamically determine the stock levels based on: #items unsold -
> #items in carts, so to protect the innocent.
I would prefer this option. How can I do this though?

Jordan Elver wrote:
>> * Tell customer B that unforuntately the products have been sold
> 
> Do you mean customer A? So, they bought something and then lost out
> because customer B bought it in the meantime?
Oops, yes Cust A. Cust B normally misses out in examples.
>> * Dynamically determine the stock levels based on: #items unsold -
>> #items in carts, so to protect the innocent.
> 
> I would prefer this option. How can I do this though?
Subtraction. :)

Assuming you have tables: User -- Cart --* LineItem -- Product
With fields like: @cart.open?, @line_item.number; @product.available

Then the number of available items for a product would be:

@product.available - LineItem.sum(''number'', :conditions =>
[''carts.open
= '', true], :include => [:carts])

Without testing, I''m not sure if the :include traverses the foreign key
in this direction, but test it.

Cheers
Nic

-- 
Posted via http://www.ruby-forum.com/.

> Subtraction. :)
Thanks for that ;)
> Assuming you have tables: User -- Cart --* LineItem -- Product
> With fields like: @cart.open?, @line_item.number; @product.available
>
> Then the number of available items for a product would be:
>
> @product.available - LineItem.sum(''number'', :conditions
=> [''carts.open
> = '', true], :include => [:carts])
I get that. But carts and cart items are not persisted to the db until
an order is created. My cart object holds an array of cart items in
the session. Maybe I should use the db more heavily and just store the
cart id in the session?

> I get that. But carts and cart items are not persisted to the db until
> an order is created. My cart object holds an array of cart items in
> the session. Maybe I should use the db more heavily and just store the
> cart id in the session?
Databases are there for us to store data so we can retrieve it at a 
later stage :)

-- 
Posted via http://www.ruby-forum.com/.

> Databases are there for us to store data so we can retrieve it at a
> later stage :)
I''ll that as a yes :) Thanks for your advice.

Cheers,
Jord

I''ll chime in a bit late here and agree that you want to store your  
carts in the database.  That''s what I have done with my store  
application and it sure makes dealing with adding line items to the  
cart a breeze.  Then when you want to check out all you have to do is  
say Sale.create(:cart => @cart). :)

As for the inventory question, what my business requirements were for  
my store were to allow selected products to "go negative" on their  
inventory levels (which are tracked in the ProductVariant model).  In  
this case, new inventory can be acquired quickly enough to allow both  
customers to complete their orders.  Customer B would get the order  
filled more promptly than customer A, but customer''s A sale would not  
be lost.

--
Benjamin Curtis
http://www.bencurtis.com/
http://www.tesly.com/ -- Collaborative test case management
http://www.agilewebdevelopment.com/ -- Resources for the Rails community



On Jul 28, 2006, at 5:52 AM, Jordan Elver wrote:
>> Databases are there for us to store data so we can retrieve it at a
>> later stage :)
>
> I''ll that as a yes :) Thanks for your advice.
>
> Cheers,
> Jord
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060728/f2516544/attachment.html

> I''ll chime in a bit late here and agree that you want to store
your carts in
> the database.  That''s what I have done with my store application
and it sure
> makes dealing with adding line items to the cart a breeze.  Then when you
> want to check out all you have to do is say Sale.create(:cart => @cart).
:)
So, your LineItem is associated with both your cart object (which you
have in the db), and  with your Sale (order) object? That sounds good.
Presumably I would need to periodically clear out the old (stale) cart
objects from the carts table.

One thing that I am not sure about from Dr Nic''s advice is how I know
whether a cart is open or not? I''m not that familier with sessions.
> As for the inventory question, what my business requirements were for my
> store were to allow selected products to "go negative" on their
inventory
> levels (which are tracked in the ProductVariant model).  In this case, new
> inventory can be acquired quickly enough to allow both customers to
complete
> their orders.  Customer B would get the order filled more promptly than
> customer A, but customer''s A sale would not be lost.
Yeah, that is probably an avenue I could explore. <goes off thinking>

Thanks for your input.
Jord

> So, your LineItem is associated with both your cart object (which you
> have in the db), and  with your Sale (order) object? That sounds good.
> Presumably I would need to periodically clear out the old (stale) cart
> objects from the carts table.
>
> One thing that I am not sure about from Dr Nic''s advice is how I
know
> whether a cart is open or not? I''m not that familier with
sessions.
Sorry to reply to myself, but I''m still struggling with this :(

1. New customer browses the site. I create a new cart and add its id
to the session. The cart exists in the carts table (which contains
only the primary key).

2. Customer browses the site and adds cart items to the cart. These
are stored in the cart items table.

3. In order to check stock levels when adding products, I search for
cart items which contain the product I am interested in and see what
has been taken. This will allow me to check what is actually available
versus the product.stock level.

4. Carts remain in the db and availability of items will not be
correct because old carts will still be present.

How do I deal with that? How can I clear out old carts so they don''t
interfere in the future?

Thanks for any help.

Cheers,
Jord

> How do I deal with that? How can I clear out old carts so they
don''t
> interfere in the future?
use a timestamp/datetime column and then run a cron job to delete
carts that have been inactive for a certain time.

What I have done in the past is to store the cart info in the session.
 Then in the table that holds user/login information I have a
last_sessionid field.  That way if a customer logs in from another
computer, I load their old session information and update the
last_sessionid.  That way the session is persistant for the user, not
the cookie.

If you do that then tracking stock is a bit more complicated.  I
actually use a separate table for that where I store the sku, number
put in cart, session id, and timestamp.  When an item is placed in the
cart I add a row, when an item is deleted I delete the row. When an
order is placed I delete the row.  I query the table periodically and
if there are entries with expired timestamps (based on busines rules)
I increment the number in stock of that sku in the products table,
delete the cart from the session, and then delete the row.  I also
have timestamps on the cart in the session, and every time the session
is loaded I check to see if the cart has expired, and if so delete it
from the session.  That way I don''t have to have a cron job deleting
expired carts, it just happens automatically.

Chris

>
> Thanks for any help.
>
> Cheers,
> Jord
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

I would suggest that you shouldn''t care about the quantity of items  
that are currently in carts.  Only when a cart has been converted  
into a sale do you put the quantity of the items in the cart on  
hold.  Only when that sale becomes a shipment do you deduct the  
quantity on hand for those same items.

--
Benjamin Curtis
http://www.bencurtis.com/
http://www.tesly.com/ -- Collaborative test case management
http://www.agilewebdevelopment.com/ -- Resources for the Rails community



On Jul 29, 2006, at 7:41 AM, Jordan Elver wrote:
> 3. In order to check stock levels when adding products, I search for
> cart items which contain the product I am interested in and see what
> has been taken. This will allow me to check what is actually available
> versus the product.stock level.

On 7/30/06, Benjamin Curtis <rails@bencurtis.com>
wrote:
> I would suggest that you shouldn''t care about the quantity of
items
> that are currently in carts.  Only when a cart has been converted
> into a sale do you put the quantity of the items in the cart on
> hold.  Only when that sale becomes a shipment do you deduct the
> quantity on hand for those same items.

perhaps already paranoid of a nascent issue, but wouldn''t the
inventory strategy discussed in this thread, be only minutes away from
a simple denial of service attack? i don''t have a better solution (or
even suggest one worse), but it seems as though a web store that used
this strategy would turn away legitimate customers, waiting for the
timeout to free up the inventory (only to have the same thing
potentially occur again).

ignoring the quantity seems like a bit of a sidestep since it would
have the same issue as mentioned above where User A places n items in
her cart then is later told that only 1 was reserved on her behalf.
this probably isn''t a big deal for high volume items but if there were
only two (think handmade goods or works) it seems like a bummer.

certainly, the number of malicious persons focusing on webstores is
perhaps limited, but limited is not non-existant right? perhaps this
is where the hotornot captcha comes into play? =)

regards,
jean-pierre

> I would suggest that you shouldn''t care about the quantity of
items
> that are currently in carts.  Only when a cart has been converted
> into a sale do you put the quantity of the items in the cart on
> hold.  Only when that sale becomes a shipment do you deduct the
> quantity on hand for those same items.
That is my original problem though. I don''t want people to checkout
and then be disapointed when the items are not in stock. To be honest,
the amount of purchases that this store will take will probably be
minimal and I''m not sure that this issue will ever arise. It''s
a
tricky one. I''m not sure if I''m trying to over engineer this,
maybe I
should launch and see what happens :)

> perhaps already paranoid of a nascent issue, but wouldn''t the
> inventory strategy discussed in this thread, be only minutes away from
> a simple denial of service attack? i don''t have a better solution
(or
> even suggest one worse), but it seems as though a web store that used
> this strategy would turn away legitimate customers, waiting for the
> timeout to free up the inventory (only to have the same thing
> potentially occur again).
Yes, this is a problem. I suppose I''m going to have to piss someone
off whatever I do. If I reserve stock in someones cart for 1 hour and
after that expire the cart, then someone will inevitably get annoyed
when their cart expires and they loose their purchases. But if I don''t
reserve stock, someone will be annoyed when they checkout and there
products are not available :)

I guess I''ll have to weigh up which is more annoying :)

A quite widely used technique is to display accurate stock levels only
if stock is, greater than, saym, 5 and  display "Less Than 5" or
"Low
Stock" for the stock level if the stock level is less than 5.
Theactual number obviously depends on the product and the turnover for
thath product. N\ormally, a low_stock_level fields would be stored
with the product description.

This way customers are pre-warned that their chosen item may not be
available once they make it to checkout.

Easy to implement, and you don''t have to worry about allocation stock
to carts. Online shops have a notoriously high rate of "cart
abandonment", so tying up stock in carts is a very bad idea.

Max