Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Storing cart items in database
03-13-2014, 12:06 AM #1
- Join Date
- Jan 2014
- Thanked 0 Times in 0 Posts
Storing cart items in database
I am looking for a way to store items in a database and looking for a good way to do it. So far I have thought of a couple of ideas:
The first is to serialise items and then store them in a table field 'cart' in the customer table.
The second is to create a table specifically for the cart system, and every time a user adds an item to their cart it adds it to the cart table and is assigned to them by their 'userid' from their account (the 'userid' field from 'customers' table), and to then something like 'SELECT * FROM cart WHERE userid = 123 AND content = "newcart"' to bring up the users current cart. Upon transaction I would then have my system alter 'newcart' to 'purchased', ready for a new cart for a future sale. The only problem I see here is over time the table might get very bloated.
I would like to hear others engineering techniques and ideas.
Last edited by Phaelon; 03-13-2014 at 12:09 AM.
03-13-2014, 02:26 AM #2
- Join Date
- Oct 2006
- Denver, Colorado USA
- Thanked 476 Times in 465 Posts
Have an 'order' table that holds information about each order/pending order (cart) and an 'order_details' table that holds the items in each order. The 'order' table would have columns for an id (the order id), the user id, date/time, and status (your 'newcart', 'purchased' values.) The order_details table would have one row for each item in each order/cart with an id, order id, item id, quantity, and status (the status field for each item is used, as desired, for things like back-ordered, shipped, returned, canceled...)
To display the cart contents for any cart (based on the user id and the status = 'newcart') simply join the order and order_details tables using the order id, and join this with your product table using the item id.
When the cart is actually ordered, you would change the 'order' table's status column from 'newcart' to 'purchased'.
You would occasionally remove old 'order' rows and the correspond order_detail rows that were never purchased, using the date/time value in the 'order' table, i.e. remove any data that still has a status of 'newcart' that is more than 30 days or so old.
Tables with a few million rows are common. Once you get to this size for a table, you would start using partitioning ( MySQL :: MySQL 5.6 Reference Manual :: 18.1 Overview of Partitioning in MySQL ) to 'archive' older data while keeping it available.Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.