SQL: shopping cart design for a marketplace [closed]

Posted on

Question :

I’m working on a marketplace that requires the user to be logged in to add items to the shopping cart. In most online examples, I can’t see the benefits of creating a separate cart and cart_item tables.

Example: https://mysql.tutorials24x7.com/blog/guide-to-design-database-for-shopping-cart-in-mysql

Why not just create an empty order and set its status to cart and when the user submits the order, then just reduce the quantities from the product table and fill the other columns with the required values and change the status of the order?

Example with shopping cart
enter image description here

Example using the order table to act as a cart when setting its status to cart.
enter image description here

Answer :

It really depends on your use cases. Having the cart and cart_item tables in the first example is just an extra level of normalization which may be needed in some contexts (depending on how different a cart_item is vs an ordet_item etc). But generally speaking, and based on the schema you’ve designed, it’s totally acceptable to eliminate the idea of a cart and cart_item entity entirely, if the order and order_item can support all your use cases for that workflow without much complication in your backend code.

You could even take it one step further and not touch the database at all until the order has been placed. Rather than storing an “in-process order” in the database, you can take the route of just storing an object representation, such as JSON on the client side, until the order is submitted (which is when in the workflow your backend code can convert that JSON to your database entities and persist it in the order and order_item tables respectively). This is one implementation some websites take, and will temporarily persist it on the client side such as in session storage (which is why you’ll find on some websites your items only temporarily stay in the cart).

But regardless, it really just depends on the use cases of your workflow whether you choose option A or B in your post, or option C that I just mentioned. They’re all valid choices.

Leave a Reply

Your email address will not be published. Required fields are marked *