Store inventory

I dont see any need of creating inventory table separately. You can simply add inventory column in products table. Add to that when new piece of the same product is added and subtract from it when a product is sold.

Schema::create('products', function (Blueprint $table) {
        $table->increments('id');
        $table->unsignedInteger('sku');
        $table->unsignedInteger('barcode');
        $table->string('name');
        $table->text('description')->nullable();
        $table->unsignedInteger('client_price');
        $table->unsignedInteger('seller_price');
        $table->text('image')->nullable();'
	$table->unsignedSmallInteger('inventory');
        $table->timestamps();
 });

If there is a scenario where quantity could be changed, then in that case you should create another inventory table. When quantity is changed, just add another record and set updated_at timestamp. You can keep track of changes in inventory and retrieve the record with latest timestamp to know the current inventory.

Schema::create('inventory', function(Blueprint $table) {
	$primary(['product_id','updated_at']);

        $table->unsignedInteger('product_id');
	$table->foreign('product_id')->references('id')->on('products');

        $table->smallUnsignedInteger('quantity');
        $table->timestamp('updated_at');        
});

You can take it one step further. You can add a nullable column sale_id. Keep the sale_id null when user changes the inventory. If a product is sold, then pass that ID to inventory table while updating the quantity. This way you can find the exact sale transaction that caused the inventory change !

Schema::create('inventory', function(Blueprint $table) {
	$primary(['product_id','updated_at']);

        $table->unsignedInteger('product_id');
	$table->foreign('product_id')->references('id')->on('products');

	$table->unsignedInteger('sale_id')->nullable();
	$table->foreign('sale_id')->references('id')->on('sales');

        $table->smallUnsignedInteger('quantity');
        $table->timestamp('updated_at');        
});

@msslgomez

@ravish

If there is a scenario when quantity could be increased after adding a product, then in that case you should create another inventory table the way you have made.

That's the logic I'm going for.

I originally had a compound index but I don't really know how to use it correctly, especially when sending the route to update.

On the last part you suggest creating a new inventory table entry every single time an item gets purchased?

Yes. I mean that would make sense for that table right? If product has 10 quantity then u add an entry in the inventory table with quantity 10 when creating the product.

Then imagine product gets sold after a day, then you add another entry to the inventory table, set quantity to 9, pass the sale_id so that in future you can know which sale caused that change.

If two of the same product gets sold after few days, you add another entry with quantity set to 7 and so on. This way you can keep track of the changes. @msslgomez

@msslgomez I’m sure I answered a pretty similar question a little while back. My advice was to create a table that holds stock level adjustments for products. The table would essentially be a ledger, with a foreign key pointing to a SKU, and the quantity that was added/removed from stock.

Say 10 units of a SKU come in. You would add a row to your stock_level_adjustments table with the foreign key for the SKU and set some column, say quantity, to 10. If you get a delivery for 10 more of the same SKU, you would create another row. You can then get the current stock level by SUM-ing the quantity for a particular SKU:

$stockLevel = $sku->stockLevelAdjustments()->sum('quantity'); // 20

You can wrap this up into a method on your Sku model:

class Sku extends Model
{
    public function stockLevelAdjustments()
    {
        return $this->hasMany(StockLevelAdjustment::class);
    }

    public function stockLevel()
    {
        return $this->stockLevelAdjustments()->sum('quantity');
    }
}

When you make a sale, you would again create a new row, but this time with a negative value representing the number sold. So if someone buys three of the above SKU, you would create a stock level adjustment for that purchase:

$sku->stockLevelAdjustments()->create([
    'quantity' => -3,
]);

Now if you SUM the quantity column, you should get 17.

@msslgomez I think that was my question also. So there wouldn't be an inventory table but a stock level adjustments table instead? Could you explain what exactly a SKU is, I do have it on my products is it just like a more complex id? Also why does the SKU get it's own model?