Shopping cart in db - order lines table or separate cart table?

Question

I want to store the cart data of my multitenant app in the database.

I am wondering if it would be better to have a separate table (a project I work on does it that way but I think they migrated from sessions to that solution) or if I should put everything in the order lines table and have an order status to decide if its part of a cart or an order.

What would be the pros and cons for each solution?

Answer 1

If, within your system, the data related to the cart are exactly the same as the order line and only differ by a status, then you could keep all in the same table and differentiate by the value of the foreign key to a status table.

However the properties related to placed orders and products of a cart usually differ. The cart related data basically hold a state for a user to resume her activities and possibly present products that are likely to be consumed (a bit unusual). On the other hand data related to order lines are important, they are connected with transactions, financial info, stock, history,trends etc, this is the data that actually matters.

So the placed orders are the data with value, that get analysed and processed, while the cart is only stored for user's convenience. Generally it is best to keep them separated for flexibility and scalability.