php - Difference between cart table and order table in a database ← (PHP, MySQL)

I am working on an online store website. I need to make a shopping cart.

I am confused, in order to make the shopping cart, I must make a cart table in my database (having the customer_id,product_id,qty,price) .. And then when the customer checks out the cart, I must make another table (order) that stores the order checked out ?? Then I must make 2 tables, one for storing the cart and one for storing the order when checked out?

Storing the checked out order in a database is logical (Since it doesn't need much updates..)

BUT.. Is storing the cart in a database efficient? Imagine whenever a customer adds an item to the cart, I must use an INSERT query, and then when a customer increment the qty or decrement, I must use an UPDATE query, and then when a customer removes a cart DELETE query... Knowing that usually customers add to cart many times and then remove without checking out.. maybe changed their minds?

What is the logical solution??

Thanks for the help

Solution:

It depends on your business. If your business requires;

• You may save in their cookie, local storage if user is not logged in to your web site.
• You may get all the cart from cookie and save into your database if user is authenticated
• If there is no-login payment then you don't have to move them between user's cookie and database.
• If the user may log-in from multiple platforms and you want to unify the cart then you need to save the cart in your database(mysql or any other one).

You may use another database technologies such as redis hashes with expire option. It is pretty fast and i think it covers all the requirements of cart.

• create a hash with the session identifier
• use EXPIRE to set time-to-live (24 hours maybe) - O(1)
• use HSET to add products with their amount - O(1) for each field/value
• use HINCRBY to increase the amount - O(1)
• use HDEL to remove products with their respective amount - O(1) for single field
• use HGETALL to get all the cart. - O(N) for number of fields.

127.0.0.1:6379> hset user:somesessionidentifier:cart productid:2 5
(integer) 1
127.0.0.1:6379> expire user:somesessionidentifier:cart 86400
(integer) 1
127.0.0.1:6379> ttl user:somesessionidentifier:cart
(integer) 86394
127.0.0.1:6379> hset user:somesessionidentifier:cart productid:3 1
(integer) 1
127.0.0.1:6379> hset user:somesessionidentifier:cart productid:92 1
(integer) 1
127.0.0.1:6379> hset user:somesessionidentifier:cart productid:5 1
(integer) 1
127.0.0.1:6379> hgetall user:somesessionidentifier:cart
1) "productid:2"
2) "5"
3) "productid:3"
4) "1"
5) "productid:92"
6) "1"
7) "productid:5"
8) "1"
127.0.0.1:6379> HINCRBY user:somesessionidentifier:cart productid:2 1
(integer) 6
127.0.0.1:6379> HDEL user:somesessionidentifier:cart productid:5
(integer) 1
127.0.0.1:6379> hgetall user:somesessionidentifier:cart
1) "productid:2"
2) "6"
3) "productid:3"
4) "1"
5) "productid:92"
6) "1"
127.0.0.1:6379>