I'm working on an e-commerce app, and while designing the database schema, I stumbled on two questions about storing cart data.
First, is there any reason to have both a cart and a cart_line table? I get why this is done with things like orders. It obviously prevents duplicate data across multiple order lines (the order number, order date, user/account, status, etc.) by substituting it instead for a single foreign key of orderID.
But the only data like this that would be shared across cart lines (that I foresee) would be the user. So instead of storing a reference to a cartID in the cart line, why not just store the userID and eliminate the cart table entirely?
Am I overlooking another benefit of having a dedicated cart table?
And then secondly, pondering that question made me wonder if I even need a cart line table. If orders have a status, why not just have an 'open' status and use open orders for user carts?
I realize that this would mean searching the (larger) order_lines table instead of the cart_lines table to retrieve the user's cart. I'm just wondering how much of a difference that would actually make. And if it's perhaps normal practice to use the orders table to store cart data.
Any feedback or discussion would be much appreciated. :)
A common reason for doing this is to allow one user to have "more than one cart." Sometimes a user does need to "park" an unfinished order and place another one before returning to the unfinished order to complete or discard it.
So, there's a one-to-many relationship between users and carts, and a one-to-many relationship between a cart and the items that it contains.
Now ... I also strongly suggest that you spend time looking at existing e-commerce frameworks in the language of your choice. Don't unnecessarily reinvent the wheel.