Transaction Numbers in a System

This content originally appeared on DEV Community and was authored by Siswoyo Siswoyo

When designing a system that handles financial or operational transactions (e.g., orders, invoices, payments, shipments), one of the most important considerations is how transaction numbers are generated and managed.

Why Transaction Numbers Are Important

• Uniqueness → Every transaction must be identifiable without confusion.
• Traceability → Makes it easy to track and audit transactions.
• Integration → When multiple services or systems are involved, transaction numbers must remain consistent.
• User Reference → Customers and staff often need a simple reference number for communication.

Common Approaches to Transaction Numbers

1. Auto-Increment Serial (Sequential Numbers)

• Example: TRX-000001, TRX-000002, TRX-000003
• Pros:
  • Simple and human-friendly.
  • Easy to track the count of transactions.
• Cons:
  • Hard to merge across multiple systems (risk of duplicate numbers).
  • Predictable numbers can expose business volume.

2. UUID (Universally Unique Identifier)

• Example: d290f1ee-6c54-4b01-90e6-d701748f0851
• Pros:
  • Guaranteed uniqueness across systems and services.
  • No collision issues when merging data.
• Cons:
  • Not user-friendly.
  • Longer to store and index in databases.

3. Hybrid Format (Prefix + Date + Sequence / UUID)

• Example: INV-20250819-0012 or PAY-2025-08-19-XYZ123
• Pros:
  • Human-readable with context (date, type).
  • Sequence helps ordering within a day.
  • Can still incorporate randomness or UUID for uniqueness.
• Cons:
  • Slightly more complex to implement.

Recommended Practice

• For internal system identifiers: use UUIDs to ensure uniqueness, especially in distributed or microservice architectures.
• For user-facing transaction numbers: generate a readable sequence with prefixes and dates (e.g., ORD-20250819-0456).
• Store both values:
  • Primary Key / Internal ID → UUID
  • Public Transaction Number → Sequential or Hybrid format

Example: Dual Identifier Approach

This way, the system remains scalable, merge-friendly, and user-friendly.

SQL Schema Example

Below is a PostgreSQL example showing how to implement this approach:

CREATE TABLE transactions (
    transaction_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Internal unique ID
    transaction_no VARCHAR(50) UNIQUE NOT NULL,                -- Public transaction number
    transaction_type VARCHAR(20) NOT NULL,                     -- e.g. ORDER, PAYMENT, INVOICE
    transaction_date TIMESTAMP NOT NULL DEFAULT NOW(),
    customer_id UUID NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
);

-- Example: Insert with a generated transaction number
INSERT INTO transactions (transaction_no, transaction_type, customer_id, amount)
VALUES (
    CONCAT('ORD-', TO_CHAR(NOW(), 'YYYYMMDD'), '-', LPAD(nextval('transaction_seq')::TEXT, 4, '0')),
    'ORDER',
    '550e8400-e29b-41d4-a716-446655440000',
    150000.00
);

-- Sequence for daily increment
CREATE SEQUENCE transaction_seq START 1;

-- Example: Insert with a generated transaction number
INSERT INTO transactions (transaction_no, transaction_type, customer_id, amount)
VALUES (
    CONCAT('ORD-', TO_CHAR(NOW(), 'YYYYMMDD'), '-', LPAD(nextval('transaction_seq')::TEXT, 4, '0')),
    'ORDER',
    '550e8400-e29b-41d4-a716-446655440000',
    150000.00
);

Relationships with Other Tables

When designing related tables, always use transaction_id (UUID) as the foreign key.
Each related table should also have its own unique reference number (payment_no, shipment_no).

Payments Table

CREATE TABLE payments (
    payment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Internal unique ID
    payment_no VARCHAR(50) UNIQUE NOT NULL,                -- Public payment number
    transaction_id UUID NOT NULL REFERENCES transactions(transaction_id) ON DELETE CASCADE,
    payment_date TIMESTAMP NOT NULL DEFAULT NOW(),
    payment_method VARCHAR(50) NOT NULL,
    amount NUMERIC(12,2) NOT NULL
);

-- Sequence for payments
CREATE SEQUENCE payment_seq START 1;

-- Example insert
INSERT INTO payments (payment_no, transaction_id, payment_method, amount)
VALUES (
    CONCAT('PAY-', TO_CHAR(NOW(), 'YYYYMMDD'), '-', LPAD(nextval('payment_seq')::TEXT, 4, '0')),
    'a9c3b5f4-2e4b-4ef9-a2af-123456789abc',
    'CREDIT_CARD',
    150000.00
);

Shipments Table

CREATE TABLE shipments (
    shipment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Internal unique ID
    shipment_no VARCHAR(50) UNIQUE NOT NULL,                -- Public shipment number
    transaction_id UUID NOT NULL REFERENCES transactions(transaction_id) ON DELETE CASCADE,
    courier VARCHAR(50) NOT NULL,
    tracking_no VARCHAR(100),
    shipped_date TIMESTAMP
);

-- Sequence for shipments
CREATE SEQUENCE shipment_seq START 1;

-- Example insert
INSERT INTO shipments (shipment_no, transaction_id, courier, tracking_no, shipped_date)
VALUES (
    CONCAT('SHP-', TO_CHAR(NOW(), 'YYYYMMDD'), '-', LPAD(nextval('shipment_seq')::TEXT, 4, '0')),
    'a9c3b5f4-2e4b-4ef9-a2af-123456789abc',
    'DHL',
    'DHL123456789',
    NOW()
);

Why Use UUID for Relations but Public Numbers for Reference?

• UUID (transaction_id, payment_id, shipment_id) → Used for database relations, stable and permanent.
• Public numbers (transaction_no, payment_no, shipment_no) → Used for communication with users, human-friendly, and can include business context (prefix, date, sequence).
• This ensures data integrity inside the system while providing clear references outside the system.

This content originally appeared on DEV Community and was authored by Siswoyo Siswoyo