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:
- User A adds an item to their cart, and I check that it’s in stock -
all good.
- User A continues to shop
- User B adds the same item to their cart, and I check the stock
again - fine again.
- User B checks out, pays for the item, and I decrement the stock
level for that product
- 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
- 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 E. 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
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
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 C.
http://www.bencurtis.com/
http://www.tesly.com/ – Collaborative test case management
http://www.agilewebdevelopment.com/ – Resources for the Rails community
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.
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 
-
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).
-
Customer browses the site and adds cart items to the cart. These
are stored in the cart items table.
-
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.
-
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
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 C.
http://www.bencurtis.com/
http://www.tesly.com/ – Collaborative test case management
http://www.agilewebdevelopment.com/ – Resources for the Rails community
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
On 7/30/06, Benjamin C. [email protected] 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
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
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