Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jan 2014
    Thanked 0 Times in 0 Posts

    Storing cart items in database

    Hi all,

    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 01:09 AM.

  2. #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Denver, Colorado USA
    Thanked 511 Times in 499 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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts