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
Example using the order table to act as a cart when setting its status to cart.
Best Answer
It really depends on your use cases. Having the
cartandcart_itemtables in the first example is just an extra level of normalization which may be needed in some contexts (depending on how different acart_itemis vs anordet_itemetc). But generally speaking, and based on the schema you've designed, it's totally acceptable to eliminate the idea of acartandcart_itementity entirely, if theorderandorder_itemcan 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
orderhas 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 theorderis submitted (which is when in the workflow your backend code can convert that JSON to your database entities and persist it in theorderandorder_itemtables 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.