Supplier Inventory: Designing the Schema (PostgreSQL) • fakhrimhd

This is Part 1 of the Supplier Inventory series.

From Spreadsheet to Schema

Remember the Excel nightmare from the main story? Dozens of sheets, everything linked by fragile VLOOKUPs, crashes when the data grew too large.

The first step to fixing that: design a proper schema.

I needed to answer:

What are the core entities?
How do they relate to each other?
What questions should the data answer?

The Entity-Relationship Model

┌──────────────┐       ┌──────────────┐       ┌──────────────┐
│   SUPPLIER   │       │   MATERIAL   │       │PRODUCTION_UNIT│
│──────────────│       │──────────────│       │──────────────│
│ supplier_id  │       │ material_id  │       │ unit_id      │
│ company_name │       │ material_name│       │ unit_name    │
│ address      │       │ description  │       │ address      │
│ city         │       │ unit         │       │ city         │
└──────┬───────┘       └──────┬───────┘       └──────┬───────┘
       │                      │                      │
       │    ┌─────────────────┼──────────────────────┤
       │    │                 │                      │
       ▼    ▼                 ▼                      ▼
┌──────────────┐       ┌──────────────┐       ┌──────────────┐
│   CONTRACT   │       │    ORDERS    │       │UNIT_INVENTORY│
│──────────────│       │──────────────│       │──────────────│
│ contract_id  │◄──────│ order_id     │       │ unit_id      │◄─┐
│ supplier_id  │       │ contract_id  │       │ material_id  │  │
│ material_id  │       │ unit_id      │       │ quantity     │  │
│ unit_id      │       │ order_date   │       │ last_updated │  │
│ quantity     │       │ required_date│       └──────────────┘  │
│ price_per_unit│      │ status       │                         │
│ contract_value│      └──────────────┘                         │
│ start_date   │                                                │
│ end_date     │       ┌──────────────┐                         │
└──────────────┘       │INVENTORY_    │                         │
                       │  MOVEMENT    │                         │
                       │──────────────│                         │
                       │ movement_id  │                         │
                       │ unit_id      │─────────────────────────┘
                       │ material_id  │
                       │ supplier_id  │
                       │ order_id     │
                       │ movement_type│
                       │ quantity     │
                       │ movement_date│
                       └──────────────┘

Table Definitions

Core Entities

Table	Purpose	Key Columns
`supplier`	Vendor master data	`supplier_id`, `company_name`, `address`, `city`, `province`, `country`
`material`	Products & raw materials	`material_id`, `material_name`, `description`, `unit`
`production_unit`	AMP sites & facilities	`unit_id`, `unit_name`, `address`, `city`, `province`

Relationship Tables

Table	Purpose	Key Columns
`contract`	Supplier agreements with auto-computed value	`contract_id`, `supplier_id`, `material_id`, `unit_id`, `quantity`, `price_per_unit`, `contract_value` (computed)
`orders`	Purchase orders linked to contracts	`order_id`, `contract_id`, `unit_id`, `order_date`, `required_date`, `shipped_date`, `status`

Inventory Tables

Table	Purpose	Key Columns
`unit_inventory`	Current stock per location	`unit_id`, `material_id` (composite PK), `quantity`, `last_updated`
`inventory_movement`	All stock movements with full traceability	`movement_id`, `unit_id`, `material_id`, `supplier_id`, `order_id`, `movement_type`, `quantity`, `movement_date`

Key Design Decisions

1. Composite Primary Key for Inventory

CREATE TABLE unit_inventory (
    unit_id      INT            NOT NULL,
    material_id  INT            NOT NULL,
    quantity     DECIMAL(18, 2) NOT NULL DEFAULT 0,
    last_updated DATE,
    PRIMARY KEY (unit_id, material_id),
    FOREIGN KEY (unit_id)     REFERENCES production_unit(unit_id),
    FOREIGN KEY (material_id) REFERENCES material(material_id)
);

Why? Stock is tracked per material per production unit. A single material can exist in multiple sites. The composite key enforces this constraint at the database level — no duplicate entries possible.

2. Computed Contract Value

CREATE TABLE contract (
    contract_id    INT             PRIMARY KEY,
    supplier_id    INT             NOT NULL,
    material_id    INT             NOT NULL,
    unit_id        INT             NOT NULL,
    quantity       DECIMAL(18, 2)  NOT NULL,
    price_per_unit DECIMAL(18, 2)  NOT NULL,
    contract_value DECIMAL(18, 2)  GENERATED ALWAYS AS (quantity * price_per_unit) STORED,
    start_date     DATE,
    end_date       DATE,
    status         VARCHAR(50)     DEFAULT 'Active',
    -- Foreign keys...
);

Why? contract_value is always quantity × price_per_unit. Using GENERATED ALWAYS AS ... STORED:

No stale data — Value updates automatically if quantity/price changes
No application logic needed — Database handles the math
Query-ready — It’s a real column, indexable and fast

3. Event-Sourced Movements with Full Traceability

CREATE TABLE inventory_movement (
    movement_id   INT            PRIMARY KEY,
    unit_id       INT            NOT NULL,
    material_id   INT            NOT NULL,
    supplier_id   INT,                        -- Who supplied it?
    order_id      INT,                        -- Which order?
    movement_type VARCHAR(20)    NOT NULL
        CHECK (movement_type IN ('RECEIPT', 'CONSUMPTION')),
    quantity      DECIMAL(18, 2) NOT NULL
        CHECK (quantity > 0),
    movement_date DATE           NOT NULL,
    notes         VARCHAR(255),
    -- Foreign keys...
);

Why this design?

Feature	Benefit
`supplier_id` reference	Trace stock back to origin — “this batch came from Supplier X”
`order_id` reference	Link to procurement — “received via Order #123”
`movement_type` enum	Only RECEIPT or CONSUMPTION — clean, explicit
`quantity` always positive	Direction from `movement_type`, not sign — clearer logic
`movement_date`	When it actually happened (not just insert time)

Sample Queries

Current stock by production unit:

SELECT
    pu.unit_name,
    m.material_name,
    ui.quantity,
    ui.last_updated
FROM unit_inventory ui
JOIN production_unit pu ON ui.unit_id = pu.unit_id
JOIN material m ON ui.material_id = m.material_id
ORDER BY pu.unit_name, m.material_name;

Contract utilization:

SELECT
    c.contract_id,
    s.company_name AS supplier,
    m.material_name AS material,
    c.quantity AS contracted_qty,
    c.contract_value,
    c.status
FROM contract c
JOIN supplier s ON c.supplier_id = s.supplier_id
JOIN material m ON c.material_id = m.material_id
WHERE c.end_date >= CURRENT_DATE
ORDER BY c.contract_value DESC;

Movement history for a material:

SELECT
    im.movement_date,
    im.movement_type,
    im.quantity,
    s.company_name AS supplier,
    im.notes
FROM inventory_movement im
LEFT JOIN supplier s ON im.supplier_id = s.supplier_id
WHERE im.material_id = 1
ORDER BY im.movement_date DESC;

What I’d Do Differently

Looking back, a few things I’d improve:

Add created_at / updated_at timestamps — Useful for debugging and auditing
Add reorder_point to unit_inventory — Enable automatic low-stock alerts
Partition inventory_movement by date — For performance at scale
Add soft deletes — deleted_at column instead of hard deletes

But for a pilot demonstrating core concepts? This schema works.

Next: Building the Interface →

Previous: ← Back to Overview

fakhrimhd/supplier-inventory

Lorem ipsum dolor sit amet, consectetur adipiscing elit.

00K00KMIT