Single or multiple mysql rows? (shopping basket)

Question

I am currently creating a custom e-commerce site (in php, but that's not really relevant for this question).

I have just got to creating the shopping basket, and cannot decide between the following 2 options:

option 1:
Basket table:

• id
• user
• items

In this option, I would have one row per user, with all of the items and quantities stored in the items field.

This format is already used in the cookie based basket for non-logged in users, so parsing of the items field is no problem.

option 2:
Basket_items table:

• id
• user
• item
• quantity

In this option, I would have one row per item in the basket.

option 3:
suggest a better idea.

conclusion

Both of these options are equally easy for me to implement, so the question comes down which would be more efficient/convenient for updating the basket.

Thank you for any answers, Nico

Answer 1

Use Option 2 - you can't realistically support changes to a shopping cart using Option 1, or report from it.

Answer 2

Option 2. This is the best option and provides good data normalisation. It will give way for possible future advanced selects and filtering of the users basket.

Answer 3

Option 2 is the way to go. Storing all items and quantities in items field (option 1) means you are going against the relational nature of MySQL. You'll have to define a format and parse it with option 1, additional code you don't have write with option 2. Also, with Option 2, you'll be able to do other things easier down the line, like calculate totals, shipping amounts, etc, as well as reporting on item quanities sold (just a simple query).

Of course, if I was writing this, I'd also ask myself if there is a library available to do this - why reinvent such common a functionality as shopping cart. I am not from PHP world, so I don't know what the options are, but I am sure there must be something you can reuse. So ultimately, I'd encourage you to choose option 3 - don't implement it yourself if you an avoid it :-)

Answer 4

Option 1 would require you to serialize the data in the items column which is generally frowned upon for complexity and performance reasons.

You're using a DB for its linking capabilities so lets use them. Your cart_items table should work out really nicely. This way each cart can point to a user and all the items in the cart can point to the cart.

Answer 5

OPTION 3

You will need, at a minimum, a basket table and a basket_items table. Shopping carts become inherently bloated and you will soon realize you're going to need more relation tables than you anticipated. Breaking the tables up in this manner provides a 1-to-many relationship for each user's basket to their items.

This will let you do things in the future like apply promotional codes to each user's basket.

basket

• id
• user_id

basket_items

• id
• basket_id
• item_id
• quantity

Answer 6

Options 2 is the preferred option.

"item_id" could be a id to a table where all items are stored (Store table) and where the complete description and other information is available for this item. But I would add a price tag to this basket for each item and often it makes sense, to add also the users session id /md5 hash to this basket. So the SQL query string for PHP to create such table, could be something like:

   $sql="CREATE TABLE ".$table_prefix."Basket (
   id int(11) NOT NULL auto_increment,
   sid varchar(50) default NULL,
   item_id int(10) default NULL,
   quantity int(10) default 1,
   price varchar(10) default NULL,
   PRIMARY KEY(id)
) $collate_charset;";

$collate_charset: something like $collate_charset="DEFAULT CHARACTER SET utf8";

$table_prefix: often useful to have a prefix for the tables like $table_prefix="myshop_";

With such table you can benefit from SQL functions like "Sum" to get a quick subtotal of a user or all users without much code ("Select Sum(price * quantity) WHERE sid = '1234'"). If this basket is also for "guests", you need another table where the session-id and creation date is stored, so you can regularly cleanup the basket from unused entries.