Shopping cart database design
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.
3) 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,