sadebo's avatar

Ecommerce store data modelling

Hello, I am a programmer coming from the world of node.js/javascript and just did my first bigger laravel project after a few smaller ones. I made a classic ecommerce project, the only thing I struggled with or found easier with javascript, was the setup of my database structure (Orders, Cart, etc.)

For the cart of my users, I didn't actually make a Cart table, but a many-to-many relationship with pivot table between users and products. For the orders, I made an Order table and OrderItem table, where the orderitems have an order_id reference to the order.

It all works, but honestly it doesn't look great even to me, so I have few questions:

  1. Obvious one, anyone have a better way of setting this up?

2)I made a many-to-many relationship between users and products, but what would I need to if I wanted 2 many-to-many relationships? For example a user can have a cart, but also a list of favorite items.

3)This is my logic for creating an order:

    public function store() {
        $order = Order::create([
            'user_id' => request()->user()->id
        ]);
        $totalAmount = 0;
        if(count(request()->user()->products) == 0) {
            return ['msg' => 'No products in cart.'];
        } else {
            foreach(request()->user()->products as $p) {
                global $totalAmount;
                $product = Product::find($p->pivot->product_id);
                $amount = $p->pivot->quantity * $product->price;
                $totalAmount += $amount;
                OrderItem::create([
                    'user_id' => request()->user()->id,
                    'product_id' => $p->pivot->product_id,
                    'order_id' => $order->id,
                    'quantity' => $p->pivot->quantity,
                    'price' => $product->price,
                    'amount' => $amount
                ]);
                request()->user()->products()->detach($p->id);
            }
            $order->update(['amount' => $totalAmount]);
            return ['msg' => $order];
        }
    }
  1. I create an order with a default amount (= money to pay) of 0.
  2. I go over the products in my cart if there are any, and look for the product in the database, because I figured I wanted to the price to be the current price.
  3. I add the amount to pay for each item to $totalAmount variable
  4. I create an orderitem with an order_id.
  5. I remove the items from my cart
  6. I update the order with an actual amount (= $totalAmount)

This works, but I don't like it one bit and I can't imagine there isn't a better approach to this, so I would be happy to hear it. This can either be a better way of writing current code (I'm relatively new to php and laravel), or a totally different setup alltogether. Thanks in advance.

0 likes
2 replies
martinbean's avatar

@sadebo Don‘t create a relation between products and orders. If a product price changes, you don’t want that price changing in a customers’ order.

Say a customer buys a Widget at $9.50. They’re charged $9.50. Then at a later date you update the price of Widget to be $8.50. If you just have a simple many-to-many relation between order items and products, then that customer’s order is going to start displaying the price as $8.50 in their order, but they’re going to look at their card receipt and see a charge for $9.50.

Instead, copy the pertinent information to your order items table when placing an order. You can have a (nullable) foreign key pointing to the product, but be sure to record the name and price of the product at time of purchase and store that information in columns in your order items table.

sadebo's avatar

@martinbean There is no relationship between orders and products. OrderItems have a price of their own, it gets set when an order is created as the price of that item on that specific moment, there is no relation there with product and the cartitem price doesn't change when the product price get's updated. The price only changes on items in your cart (that's the many-to-many relationship between users and products) which makes sense I guess, since those products haven't been ordered yet.

Please or to participate in this conversation.