Shopping cart database design

Databases
1

Hi there,

I am programming a site which sells second-hand watches. Every item on the site is unique.

So far I’m using a carts table in a MySQL database to hold the contents of shopping carts, but I don’t really think its necessary, since there is only one of each product.

I did think about adding a status column to the products table, which would indicate if an item is on sale, pending sale (ie in someone’s cart), or sold. That way when the PHP script generates the products pages, it only selects items which are on sale, (so customers cannot put an item in their cart which is already in someone else’s). Also I would then have a user_session_id column to indicate who’s cart its in. When it comes time for the user to go through the payment process his cart contents are generated by selecting all products with his session id. When the order is placed, an order id would be generated and inserted into an order_id column also on the products table, and then we’re pretty much done. (The orders table would keep a reference of the user_session_id, and customer details (shipping address, billing address etc.)

However, a few issues arise:

  1. Person A and person B both decide to put the same watch in their respective shopping carts at the same time. What happens to the database? How will it respond to that situation? Presumably the item would suddenly disappear from Person A’s cart, and the site altogether, which may well confuse the hell out of him.

  2. Let’s say Person A puts a watch in his shopping cart. It is now ‘off the shelf’ and unavailable to other potential shoppers. Person A finally decides, actually he doesn’t want the watch, and instead of helpfully emptying his cart, he just closes the browser window. The status of the watch is still pending, and so the watch is lost to the world, never to be sold.

Ideally there should be a time limit on how long items can be pending before their status reverts back to “on sale”. Another column that stores a timestamp of when the status became pending, say time_added_to_cart or something. When the product page scripts are run, the database is queried, and any items that have a user_session_id AND whose time_added_to_cart <= NOW() - INTERVAL 10 MINUTES have their status column updated to “on sale”, before the “on sale” products are retrieved. But that seems quite server intensive.

  1. Finally, what happens to sold watches? Should they be deleted from the database, or stored for future reference? I would imagine keeping a record of sold watches is beneficial, but what happens in a couple of year’s time, the database could potentially be massive. Is this an issue, or should I not worry. I’m not sure what traffic will be like, and I’m not sure how many sales are likely to be made in a year. I guess no-one really does until the site’s been live for a year.

Ok, many thanks for your input,
Mike

2

  1. Lock the database table whilst the first transaction is being registered, this will prevent a second person putting the same item in their basket.

  2. Timeout, once a user has been inactive (ie no html requests) for 5/10/15 mins you log them out and return their items back to stock, in teh case where you are selling unique items, I would add a script that allowed the second potential buyer to log their email address when the item they want is in basket but not sold, that way when that item comes back on the market an email could be sent to them informing them of the new availability.

  3. Depends what else the database is doing, if its handling the invoicing or accounts then yes you need to keep those records, if its purely for the sale and there are other paper (or electronic) systems recordning the sale and watch details, then I guess your free to delete the records and images etc. Same if you give your clients a ‘My Account’ to view previous purchases etc, then youd need to keep these records. If your concerned, move the sold items into a seperate table so they dont slow down searches on the for sale items table.

3

Ah yes Mandes, sage and wise advice indeed.

Many thanks,
Mike