Inventory System

A warehouse inventory management system tracking products, stock levels, movements between warehouses, and stock adjustments.

ERD Concept

Many-to-Many + One-to-Many: Products exist across multiple warehouses with stock levels. Stock movements track inventory changes. Adjustments record corrections.

Tables

warehouses

id name code address is_active timestamps

inventory

id product_id (FK) warehouse_id (FK) quantity reserved_quantity timestamps

stock_movements

id product_id (FK) from_warehouse_id (FK nullable) to_warehouse_id (FK nullable) quantity type reference notes user_id (FK) timestamps

Relationships

Inventory belongsTo Product
Inventory belongsTo Warehouse
StockMovement belongsTo Product
StockMovement belongsTo Warehouse (from)
StockMovement belongsTo Warehouse (to)
StockMovement belongsTo User

Migration

Schema::create('warehouses', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('code')->unique();
    $table->text('address')->nullable();
    $table->boolean('is_active')->default(true);
    $table->timestamps();
});

Schema::create('inventory', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained()->cascadeOnDelete();
    $table->foreignId('warehouse_id')->constrained()->cascadeOnDelete();
    $table->integer('quantity')->default(0);
    $table->integer('reserved_quantity')->default(0);
    $table->timestamps();
    $table->unique(['product_id', 'warehouse_id']);
    $table->index('quantity');
});

Schema::create('stock_movements', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained()->cascadeOnDelete();
    $table->foreignId('from_warehouse_id')->nullable()->constrained('warehouses');
    $table->foreignId('to_warehouse_id')->nullable()->constrained('warehouses');
    $table->integer('quantity');
    $table->enum('type', ['transfer', 'receive', 'adjust', 'sell', 'return']);
    $table->string('reference')->nullable();
    $table->text('notes')->nullable();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->timestamps();
    $table->index(['product_id', 'type']);
});