@ramniksingh You first need to pick out the entities involved in the domain. Based on your overview, there are products and departments.
For tracking the history, you should probably go for a transaction-based approach, where you record the stock movements rather than just have a table with integer values. As if you see a stock level of “50”, then you have no way of knowing when stock was transferred in, out, how many units, etc.
So, have a table that records any stock coming in and out of a department. You can then get the stock level for any particular product in any given department by sum-ing the quantity column:
Schema::create('stock_movements', function (Blueprint $table) {
$table->id();
$table->foreignId('product_id')->constraint();
$table->foreignId('department')->constraint();
$table->integer('quantity');
});
// Get stock level for product 123 in department 45:
$stockLevel = StockMovement::where('product_id', '=', 123)->where('department_id', '=', 45)->sum('quantity');
You could create local scopes to simplify the query:
$stockLevel = StockMovement::forProduct($product)->inDepartment($department)->sum('quantity');
Or create some other method that encapsulates the query entirely:
$stockLevel = $product->stockLevelIn($department);
Obviously it would be a good idea to index the columns in this query for performance.
If you find querying becomes slow, then you could create “snapshots” of stock levels that are built from your movements table (where the stock level at that time is stored in the database as a simple integer value) and query that table. This way you have stock levels (but at the cost of being out of date until the next snapshot is generated), but still have the entire history of movement of stock between departments.