Managing a **Huge Mix of Products** – The Theory Behind the Practice

Managing a Huge Mix of Products – The Theory Behind the Practice

Below is a compact “play‑book” that explains what you have to think about, how the biggest open‑source / commercial ERP/POS systems solve it, and how you can implement a flexible solution for Khoji Nepal.
Everything is written in plain language so you can use it as a reference while you design your data model and UI.

---

1️⃣ The Core Concepts every inventory system needs

Concept	What it means	Why it matters	Typical implementation
Product / Item	The thing you sell (fruit, laptop, service, etc.)	The entry point for every other rule	A single table products (or items) with a unique SKU/ID
Product Type	Stockable (real inventory), Consumable (no stock move needed), Service (no physical stock)	Determines whether you track quantity, serial/lot, expiration, etc.	Enum field type (stockable, consumable, service)
Category / Attribute Set	Logical grouping (Fruits, Electronics, Fast‑Food, etc.) + a template of attributes that apply to that group	Gives you per‑category fields without creating a new table for every kind	categories table + category_attributes (or JSON schema)
Variant	A concrete version of a product obtained by changing one or more attributes (size, colour, flavour, weight)	Allows you to keep a single “master” product but track inventory at the granular level	product_variants table with a JSON column attributes (e.g. {color:"red",size:"M"})
SKU / Barcode	Unique code that identifies a specific variant	Needed for scanning, reporting, and linking to inventory moves	Auto‑generated from product‑code‑variant‑attributes
Inventory Policy (how you track it)	None, Quantity only, Lot/Batch, Serial number	Drives the tables you need and the UI you present	Enum tracking (none, quantity, lot, serial)
Location / Warehouse	Physical place where stock lives (store, pantry, showroom, mobile‑app virtual “stock”)	Enables multi‑site businesses, stock transfers, and location‑aware fulfillment	locations table, stock_levels linking variant ↔ location
Stock Move	Any increase/decrease of quantity (purchase, sale, adjustment, transfer, scrap)	Gives an audit trail and drives real‑time stock levels	stock_moves table (date, qty, src_loc, dst_loc, reason)
Expiration / Shelf‑life	Date after which a perishable item must be sold/discarded	Required for food, medicines, cosmetics	lot table with expire_at column, FIFO/FEFO picking logic
Serial Numbers	Individual identifier for high‑value items (laptop, phone, kitchen appliance)	Needed for warranty, service, traceability	serial_numbers table linked to stock_moves
BOM / Kit	A product that is a composition of other items (e.g. “Fruit Basket”, “Computer Set”)	Allows you to explode a sale into component inventory adjustments	Bill‑of‑Materials table linking parent product → child items
Pricing / Costing Method	FIFO, LIFO, Moving Average, Standard Cost	Determines how you value your inventory and COGS	Calculation in accounting module; not a UI concern for stock

---

2️⃣ How the Big‑Name Systems actually implement these ideas

System	Product Model	Variant Handling	Tracking Options	Perishables	Services	UI / UX Highlights
Odoo (v17)	product.template (master) + product.product (variants)	Variant attributes are defined on the template → each variant gets a separate record with its own barcode, price & stock	type = Storable, Consumable, Service; tracking = None, Lot, Serial (field tracking)	Lot management (batch numbers, expiry dates) – you enable “Lots & Serial Numbers” on a product template	Services are type = service → no stock moves are created	Very visual “Product Variants” grid, quick “Add a new attribute value” button, auto‑generated barcodes
ERPNext	Item Doctype (single table) + Item Variant (via “Item Attribute”)	Attributes are defined globally → each combination creates a Item Variant record	Has Serial No, Has Batch No flags; Is Stock Item flag controls tracking	Batch/expiry dates stored in Batch DocType; FIFO/FEFO picking is a setting	Is Service Item flag disables stock ledger entries	“Item Attribute” wizard, batch creation UI, built‑in “Stock Ledger” report
Lightspeed (POS)	Product + Variant (size/color)	Variant is a separate SKU under the same product	Simple quantity tracking per variant; optional “Serial numbers” for high‑value items	No native batch/expiry UI – you store it as a custom field or separate “inventory” add‑on	Services are just “non‑stocked products”	Mobile‑first POS UI, quick “Add variant” modal, barcode scanning
Square (POS)	CatalogObject (Item) + ItemVariation	Variation is a child object with its own SKU	Quantity tracking per variation; optional “serial numbers” for items such as devices	No built‑in batch/expiry; you can add a custom attribute called “Expiration”	Services = “Item” with inventory_enabled = false	One‑click “Add variation”, inline image upload, automatic stock sync across devices

What they share

1. A single master product that holds generic data (name, description, category).
2. A variant table that stores the concrete SKU + stock‑related fields.
3. A flag (type/tracking) that tells the system whether and how to create inventory ledger entries.
4. Optional extensions (lot, serial, expiration) that are turned on per product/variant.

That is the pattern you should copy.

---

3️⃣ The “Reality Map” for Your Use‑Case

Domain	Typical Attributes	Tracking Needed?	Suggested Settings
Fruits / Vegetables	Weight, freshness, origin, organic flag	Yes – quantity, lot for batch, expiry for shelf‑life	type = stockable, tracking = lot, lot has expire_at
Fast‑Food (prepared meals)	Portion size, cooking date, allergens	Yes – quantity, lot for batch produced at a certain time	type = stockable, tracking = lot, expiry = “best‑before”
Electronics (laptop, keyboard)	Model, specs, warranty period	Yes – quantity plus serial numbers for warranty	type = stockable, tracking = serial
Appliances (fridge, washer)	Capacity, energy rating	Yes – quantity + serial (optional)	type = stockable, tracking = serial or quantity (if you don’t need per‑unit warranty)
Services (delivery, installation)	Duration, price, provider	No physical stock	type = service, tracking = none
Grocery “bulk” items (noodles, biscuits)	Pack size, brand	Usually you don’t track each individual pack, you track the container or shelf level	type = consumable (or stockable with quantity only), tracking = none – just a “stock count” updated manually or via periodic stock take
Kits / Bundles (fruit basket, computer set)	List of components	Yes – when sold you need to deduct each component	Use a Bill‑of‑Materials (BOM) on the master product; system automatically creates the child stock moves

Bottom line:

• All physical things are stockable (they have a quantity).
• Only those that need traceability (lot/serial) get that extra flag enabled.
• Services are a different type and never create stock moves.

---

4️⃣ A Practical Data Model You Can Implement Today

Below is a minimal set of tables (Laravel migration style) that mirrors the pattern used by Odoo/ERPNext but stays simple enough to code quickly.

/* 1. Core product (master) */
products
  id PK
  name VARCHAR
  description TEXT
  category_id FK → categories.id
  type ENUM('stockable','consumable','service')
  tracking ENUM('none','quantity','lot','serial')   -- only relevant when type = 'stockable'
  is_active BOOLEAN
  created_at, updated_at

/* 2. Category – also stores attribute definitions */
categories
  id PK
  name VARCHAR
  parent_id FK → categories.id  (optional hierarchy)
  attribute_schema JSON        -- e.g. {"color":["red","green"],"size":["S","M","L"]}

/* 3. Variant – concrete SKU */
product_variants
  id PK
  product_id FK → products.id
  sku VARCHAR UNIQUE
  attributes JSON               -- e.g. {"color":"red","size":"M"}
  barcode VARCHAR               -- for scanning
  purchase_price DECIMAL
  sale_price DECIMAL
  stock_quantity INT           -- only used when tracking = quantity
  created_at, updated_at

/* 4a. Optional: Lot (batch) for perishable items */
product_lots
  id PK
  variant_id FK → product_variants.id
  lot_number VARCHAR
  quantity INT
  expire_at DATE               -- NULL if not perishable
  created_at, updated_at

/* 4b. Optional: Serial numbers for high‑value items */
product_serials
  id PK
  variant_id FK → product_variants.id
  serial_number VARCHAR UNIQUE
  is_sold BOOLEAN
  created_at, updated_at

/* 5. Locations (warehouses / store fronts) */
locations
  id PK
  name VARCHAR
  address TEXT
  parent_id FK → locations.id   (optional hierarchy)

-- Stock on hand per location (calculated from stock_moves, but kept materialised for fast reads)
stock_levels
  id PK
  variant_id FK → product_variants.id
  location_id FK → locations.id
  quantity INT

/* 6. Stock moves – the immutable ledger */
stock_moves
  id PK
  variant_id FK → product_variants.id
  lot_id FK → product_lots.id      (nullable)
  serial_id FK → product_serials.id (nullable)
  qty INT                         -- positive = inbound, negative = outbound
  src_location_id FK → locations.id  (nullable for purchases)
  dst_location_id FK → locations.id  (nullable for sales)
  reference VARCHAR               -- sale order #, purchase #, adjustment comment
  created_at TIMESTAMP

How it works in practice

Action	What tables are touched
Purchase (receive goods)	Insert a stock_move with qty > 0, src_location = null, dst_location = warehouse. If tracking = lot, also create a product_lots row and reference it.
Sale	Insert a stock_move with qty < 0, dst_location = null, src_location = warehouse. If tracking = serial, pick the serial row, mark is_sold = true.
Transfer	Two stock_moves (or one with both src/dst) adjusting stock_levels at each location.
Adjustment / Stock Take	Insert a positive or negative stock_move with a special reference = “Stock‑take”.
Bundle / Kit Sale	System reads the product’s BOM (a separate table linking a parent product to child variants + qty), then creates a stock_move for each component (negative).

You can extend the model with a product_boms table if you need bundles.

---

5️⃣ UI / Workflow Recommendations for Khoji Nepal

1. Product Creation Wizard

   • Choose Category → system loads the attribute schema (e.g., color, size, weight).
   • Decide Type (stockable, consumable, service).
   • If stockable, set Tracking (quantity, lot, serial).
   • The wizard automatically creates the master record and a default variant (SKU = auto‑generated).

2. Variant Grid

   • Show each SKU as a row with editable price, stock quantity, barcode.
   • For lot tracking, include a “Add Lot” button that opens a small form (lot_number, quantity, expire_at).
   • For serial tracking, a “Add Serial” modal.

3. Stock Dashboard

   • Global “Current Stock” view aggregated by product, plus filters for low‑stock, expiring soon, serial numbers pending warranty.
   • Use colour‑coded tags: green = healthy, orange = near expiry, red = out of stock.

4. Sale Interface (POS / Mobile)

   • When a user selects a product, the UI automatically shows the correct variant (e.g., apple “Red Fuji 1 kg”).
   • If the product has lot tracking, the system picks the first‑expiring lot (FEFO) – you can expose “lot” dropdown for manual selection if needed.
   • If the product has serial tracking, a “Pick Serial” pop‑up lists available serial numbers.

5. Reporting

   • Fast‑food / perishables: daily “Expired / Near‑expiry” report.
   • Electronics: “Serial numbers sold this week” for warranty follow‑up.
   • Grocery bulk: simple “Stock count” report (no lot/serial).

---

6️⃣ What to Do About “Grocery items that you never track individually”

• Treat them as consumable (Odoo) or stockable with tracking = none (ERPNext).
• Only store a single quantity for the SKU (e.g., “500 g packets of noodles”).
• No lot/serial tables are created – you just update the stock_quantity column when you receive a new shipment or do a physical count.
• If you later decide you need batch info (e.g., for a recall), you can enable lot tracking on that SKU without altering the rest of the model.

---

7️⃣ Quick Checklist for Your Project

✅	Item
✅ Product Master – single table (products).
✅ Variant Table – holds SKUs and attribute JSON.
✅ Tracking Flags – type + tracking.
✅ Lot & Serial Tables – optional, only created when a product enables them.
✅ Location & Stock Levels – for multi‑store support.
✅ Stock Move Ledger – immutable audit trail.
✅ BOM / Kit support – optional table for bundles.
✅ UI Wizard – category‑driven attribute schema, tracking selector.
✅ Reporting – low‑stock, expiry, serial‑warranty, consumables count.

---

8️⃣ TL;DR – The “One‑Size‑Fits‑All” Recipe

1. Define a master product (type, category).
2. Add a variant record for every concrete combination of attributes (size, colour, weight).
3. Enable the right tracking mode per product:
   • None → only a quantity field (groceries, consumables).
   • Lot → batch number + expiry (fruits, fast‑food).
   • Serial → unique IDs (electronics, appliances).
4. Log every inventory movement in a stock_moves table – this drives the real‑time stock level.
5. Add extra tables only when needed (lots, serials, BOM).
6. Build the UI around the flags – the same form works for all product types; fields appear/disappear based on the selected tracking option.

That’s exactly how Odoo, ERPNext, Lightspeed, and Square keep a single code‑base flexible enough to sell a banana and a laptop, while giving each product the right level of control.

---

9️⃣ Next Steps for Khoji Nepal

1. Add the tables above to your Laravel backend (migration files).
2. Expose a REST/GraphQL endpoint that returns a product’s type and tracking flags – the front‑end can then render the appropriate UI.
3. Create a Category‑Attribute wizard in the admin UI (use a JSON schema stored in categories.attribute_schema).
4. Implement the stock‑move service (Laravel service class) that receives a purchase, sale, or adjustment request and writes the proper rows.
5. Build a simple “Inventory Dashboard” (React + Tailwind) that shows low‑stock alerts, expiring lots, and serial‑number status.

Feel free to ask for any part of the implementation (migration files, API design, or UI components) and I’ll gladly write the code for you.