1: Should i use a shopping cart schema with — cart table and a cart_items table and why?
I see that most people recommended creating a cart table and a cart_items table since the cart table and product table have a many to many relationship… makes sense in theory but I’m not seeing how or why I should use it in my case.
Right now I have my design slightly different. (See image below.)
Here Is my database Design.
**
**:
FYI the cart_items and shopping_session tables are temporary tables.
Coming from a front end perspective my data-flow would go like so:
I would pre fetch the user cart on login/sign up and store a -1 or 1 in the session cookie to indicate if the user has items in cart_items — {“authorized”:true,“user”:{“id”:12,“cart”:-1}}.
Then store the cart items in state.
When a user adds an item to the cart or updates the quantity This would be handled in client state (redux store). Then at some point I would update the db with a optimistic update.
Query’s could look like such:
SELECT
products.name,
products.description,
products.price,
quantity,
product.price * quantity AS total
FROM commerce.cart_items
INNER JOIN commerce.products
ON commerce.products.id = commerce.cart_items.prod_id
WHERE user_id = 12;
Or
UPDATE commerce.cart_items SET quantity = quantity + $(quantity) WHERE user_id = $(user_id) AND prod_id = $(prod_id)
problem: a user could clear their cookies/state after the database has been updated.
Solution: if state is cleared fetch cart options before adding items to cart(in client state).
another problem: Duplicate rows in cart_items such as:
Cart_items
prod_id| user_id| quantity
-----------------------------
| 200 | 12 | 4 |bad
| 200 | 12 | 3 |bad
| 200 | 11 | 1 |
solution I don’t see this happening if handled correctly on the front end… Although I’m pretty sure I am trying to design a relation DB as a document model instead of a relational model.
As you can see I am having trouble seeing how the front end and back end need to work together and if my back-end design is going to hinder my development later on.