96. Case Study: Inventory Management System

Inventory management is a fundamental component of many businesses, from small retail shops to multinational corporations. A well-managed inventory system not only streamlines the supply chain but also minimizes waste and asset loss. In this article, I’ll walk through a case study of how to design, implement, and simulate a simple Inventory Management System from the perspective of a professional engineer.

We’ll dig into the system design, sample code implementation in Python (using SQLite as storage), the data flow approach, and a simple simulation. Let’s get started!

1. System Requirements Analysis

Before writing a single line of code, it’s important to define the specific scenario:

Inventory System Requirements:

• Store item data (name, code, category, stock, purchase price, selling price)
• Record incoming items (stock additions) and outgoing items (sales/requests)
• Keep a transaction history (stock change log)
• Provide a current inventory report

The following table defines the requirements and the key fields:

2. Simple Architecture Design

For this case study, we’ll use a simple monolithic architecture with an SQLite database. Consider the following schema:

erDiagram
    BARANG {
        INT id PK
        STRING kode
        STRING nama
        STRING kategori
        INT stok
        FLOAT harga_beli
        FLOAT harga_jual
    }
    TRANSAKSI {
        INT id PK
        INT barang_id FK
        INT jumlah
        STRING tipe
        DATE tanggal
    }
    BARANG ||--o{ TRANSAKSI: memiliki

erDiagram
    BARANG {
        INT id PK
        STRING kode
        STRING nama
        STRING kategori
        INT stok
        FLOAT harga_beli
        FLOAT harga_jual
    }
    TRANSAKSI {
        INT id PK
        INT barang_id FK
        INT jumlah
        STRING tipe
        DATE tanggal
    }
    BARANG ||--o{ TRANSAKSI: memiliki

3. System Operational Flow

A typical workflow for this system involves the following:

flowchart TD
    A[User Input] --> B{Tambah Barang?}
    B -- Ya --> C[Tambah Data Barang baru]
    B -- Tidak --> D{Stok Masuk?}
    D -- Ya --> E[Update Stok & Catat Transaksi Masuk]
    D -- Tidak --> F{Stok Keluar?}
    F -- Ya --> G[Cek Stok & Catat Transaksi Keluar]
    F -- Tidak --> H[Laporan Inventaris]
    E --> H
    G --> H
    C --> D

flowchart TD
    A[User Input] --> B{Tambah Barang?}
    B -- Ya --> C[Tambah Data Barang baru]
    B -- Tidak --> D{Stok Masuk?}
    D -- Ya --> E[Update Stok & Catat Transaksi Masuk]
    D -- Tidak --> F{Stok Keluar?}
    F -- Ya --> G[Cek Stok & Catat Transaksi Keluar]
    F -- Tidak --> H[Laporan Inventaris]
    E --> H
    G --> H
    C --> D

4. Sample Code Implementation (Python + SQLite)

Here’s a simple example using Python (via the sqlite3 module):

a. Creating the Database and Tables

 1import sqlite3
 2
 3conn = sqlite3.connect('inventaris.db')
 4c = conn.cursor()
 5
 6# Create the items table
 7c.execute("""
 8CREATE TABLE IF NOT EXISTS barang (
 9    id INTEGER PRIMARY KEY AUTOINCREMENT,
10    kode TEXT UNIQUE,
11    nama TEXT,
12    kategori TEXT,
13    stok INTEGER,
14    harga_beli REAL,
15    harga_jual REAL
16)
17""")
18
19# Create the transactions table
20c.execute("""
21CREATE TABLE IF NOT EXISTS transaksi (
22    id INTEGER PRIMARY KEY AUTOINCREMENT,
23    barang_id INTEGER,
24    jumlah INTEGER,
25    tipe TEXT,
26    tanggal TEXT,
27    FOREIGN KEY (barang_id) REFERENCES barang(id)
28)
29""")
30
31conn.commit()

b. Basic CRUD Functions

Adding Item Data

1def tambah_barang(kode, nama, kategori, stok, harga_beli, harga_jual):
2    c.execute("INSERT INTO barang (kode, nama, kategori, stok, harga_beli, harga_jual) VALUES (?, ?, ?, ?, ?, ?)",
3            (kode, nama, kategori, stok, harga_beli, harga_jual))
4    conn.commit()

Processing Incoming/Outgoing Stock

 1from datetime import datetime
 2
 3def transaksi_barang(kode, jumlah, tipe):   # tipe: "masuk" (incoming) or "keluar" (outgoing)
 4    c.execute("SELECT id, stok FROM barang WHERE kode=?", (kode,))
 5    row = c.fetchone()
 6    if not row:
 7        print("Item not found.")
 8        return
 9    barang_id, stok_sekarang = row
10    if tipe == "masuk":
11        stok_baru = stok_sekarang + jumlah
12    elif tipe == "keluar":
13        if stok_sekarang < jumlah:
14            print("Insufficient stock.")
15            return
16        stok_baru = stok_sekarang - jumlah
17    else:
18        print("Invalid transaction type.")
19        return
20    
21    # Update the item's stock
22    c.execute("UPDATE barang SET stok=? WHERE id=?", (stok_baru, barang_id))
23    
24    # Record the transaction
25    tanggal = datetime.now().isoformat()
26    c.execute("INSERT INTO transaksi (barang_id, jumlah, tipe, tanggal) VALUES (?, ?, ?, ?)", 
27              (barang_id, jumlah, tipe, tanggal))
28    conn.commit()

c. Displaying the Inventory Report

1def laporan_inventaris():
2    print(f"{'Kode':<10} {'Nama':<20} {'Stok':<6} {'Harga Jual':<10}")
3    print("="*48)
4    for row in c.execute("SELECT kode, nama, stok, harga_jual FROM barang"):
5        print(f"{row[0]:<10} {row[1]:<20} {row[2]:<6} {row[3]:<10}")

5. Usage Simulation

Let’s simulate a real-world scenario:

 1# Add item data
 2tambah_barang("A001", "Mouse Wireless", "Elektronik", 20, 35000, 50000)
 3tambah_barang("B002", "Keyboard USB", "Elektronik", 15, 50000, 75000)
 4
 5# 10 wireless mice coming into stock
 6transaksi_barang("A001", 10, "masuk")
 7# 5 USB keyboards going out of stock
 8transaksi_barang("B002", 5, "keluar")
 9
10# Print the report
11laporan_inventaris()

Simulation Output:

1Kode       Nama                 Stok   Harga Jual
2================================================
3A001       Mouse Wireless       30     50000.0   
4B002       Keyboard USB         10     75000.0   

6. Discussion: Practices and Challenges

The system above is already usable for basic inventory needs. In real-world deployments, however, several engineering challenges often come up, including:

• Concurrency: In a multi-user system, race conditions can occur during stock updates, so implementing locking or transactions in the DBMS is essential.
• Audit Trail: For compliance audits, the stock change trail must be immutable (it cannot be deleted or altered).
• Automatic Notifications: The system can be extended to alert when an item’s stock falls below a certain threshold.
• Integration: Integrating with e-commerce, ERP, and accounting systems greatly increases the system’s value.

7. Conclusion

Building a reliable Inventory Management System is not just a matter of implementing CRUD; it also involves thinking about data integrity, scalability, and ease of integration with other systems. This case study is merely a simple introduction. If you want to expand the system, consider using Design Patterns (such as Repository or CQRS), a REST API, or even event sourcing within a microservices architecture.

I hope this sample code and the case study walkthrough help you, whether you’re learning to build an internal system or an enterprise-scale one. Don’t hesitate to extend and adapt it to your own needs!

References:

• The Pragmatic Programmer
• Designing Data-Intensive Applications
• Python sqlite3 documentation

Happy coding 🚀