Case Study 1: Schema Design for a New Microservices Application — Meridian Bank's Digital Wallet Platform

Background

Meridian National Bank's digital transformation team, led by VP of Engineering Priya Ramanathan, has been tasked with building "MeridianPay" — a digital wallet and peer-to-peer payment platform. The platform will allow Meridian customers to send money to each other, split bills, and make contactless payments at participating merchants.

The project has a six-month timeline to launch a minimum viable product (MVP). The architecture team has chosen a microservices approach, with each service owning its own database. Three services need DB2 schemas:

1. Wallet Service: Manages digital wallets linked to Meridian bank accounts
2. Payment Service: Processes peer-to-peer payments and merchant transactions
3. Notification Service: Tracks payment notifications and user preferences

The platform will run on DB2 for LUW 11.5, deployed on Kubernetes via Db2 on Cloud Pak for Data.

Projected Volumes (First 12 Months)

The Challenge

Ramanathan's team includes experienced Java and Go developers, but none of them have significant DB2 DDL experience. Their first schema draft, written by a senior backend developer named Marcus Chen, looks like this:

CREATE TABLE wallet (
    id            SERIAL,
    user_id       INT,
    balance       FLOAT,
    currency      VARCHAR(50),
    status        VARCHAR(100),
    created       VARCHAR(30),
    updated       VARCHAR(30),
    PRIMARY KEY (id)
);

CREATE TABLE payment (
    id            SERIAL,
    from_wallet   INT,
    to_wallet     INT,
    amount        FLOAT,
    type          VARCHAR(255),
    status        VARCHAR(255),
    note          TEXT,
    created       VARCHAR(30)
);

CREATE TABLE notification (
    id            SERIAL,
    user_id       INT,
    message       TEXT,
    read          BOOLEAN,
    created       VARCHAR(30)
);

Ramanathan, having read Chapter 11 of a certain DB2 textbook, immediately recognizes several problems. She schedules a design review.

The Design Review

Ramanathan opens the review by listing the issues she has identified:

Issue 1: FLOAT for Money

"Marcus, the balance and amount columns use FLOAT. We covered this in our DB2 training — binary floating-point cannot represent decimal fractions exactly. A balance of $100.10 might be stored as 100.09999999999999. Across millions of transactions, these rounding errors accumulate. We need DECIMAL(15,2)."

Issue 2: VARCHAR Oversizing and Dates as Strings

"Currency is VARCHAR(50), but ISO 4217 currency codes are always 3 characters — CHAR(3). Status is VARCHAR(255) with no constraint — what stops someone from inserting 'banana' as a status? And all the timestamp columns are VARCHAR(30). DB2 has a TIMESTAMP type that gives us date arithmetic, range queries, and 4 bytes less storage per row."

Issue 3: No Constraints

"There are no FOREIGN KEY constraints, no CHECK constraints, no UNIQUE constraints, and no NOT NULL constraints. The wallet table has no relationship to the payment table. Nothing prevents a payment with a negative amount or a wallet with an invalid status."

Issue 4: No Named Constraints

"The only constraint is the unnamed PRIMARY KEY. When an error occurs in production at 3 AM, the on-call engineer needs to know immediately which constraint was violated and on which table. Named constraints are non-negotiable."

Issue 5: SERIAL vs. Identity Columns

"SERIAL is a PostgreSQL convention. DB2 uses GENERATED ALWAYS AS IDENTITY for auto-incrementing columns. We need to use the correct DB2 syntax."

Issue 6: No Indexes Beyond Primary Keys

"There are no indexes on foreign key columns or frequently queried columns. Every query that filters by user_id, wallet_id, or payment status will do a full table scan."

Issue 7: No Audit Columns or Row Change Tracking

"We have no way to know when a row was last modified except by parsing the VARCHAR timestamp — which is not a real timestamp. We need created_at and updated_at as proper TIMESTAMP columns."

The Redesigned Schema

The team collaborates on a revised schema. Here is the wallet service design:

-- Wallet Service Schema
CREATE TABLE mpay.wallet (
    wallet_id       BIGINT         NOT NULL GENERATED ALWAYS AS IDENTITY
                                    (START WITH 1000, INCREMENT BY 1, CACHE 50),
    customer_id     INTEGER        NOT NULL,
    account_id      INTEGER        NOT NULL,
    wallet_name     VARCHAR(50)    NOT NULL DEFAULT 'My Wallet',
    currency_code   CHAR(3)        NOT NULL DEFAULT 'USD'
                                    CONSTRAINT chk_wallet_currency
                                    CHECK (currency_code IN ('USD','EUR','GBP','CAD','MXN')),
    balance         DECIMAL(15,2)  NOT NULL DEFAULT 0.00,
    daily_limit     DECIMAL(10,2)  NOT NULL DEFAULT 2500.00,
    wallet_status   CHAR(1)        NOT NULL DEFAULT 'A'
                                    CONSTRAINT chk_wallet_status
                                    CHECK (wallet_status IN ('A','S','F','C')),
    created_at      TIMESTAMP      NOT NULL DEFAULT CURRENT TIMESTAMP,
    updated_at      TIMESTAMP      NOT NULL DEFAULT CURRENT TIMESTAMP,
    CONSTRAINT pk_wallet PRIMARY KEY (wallet_id),
    CONSTRAINT fk_wallet_customer FOREIGN KEY (customer_id)
        REFERENCES meridian.customer (customer_id) ON DELETE RESTRICT,
    CONSTRAINT fk_wallet_account FOREIGN KEY (account_id)
        REFERENCES meridian.account (account_id) ON DELETE RESTRICT,
    CONSTRAINT chk_wallet_balance CHECK (balance >= 0),
    CONSTRAINT chk_wallet_limit CHECK (daily_limit > 0 AND daily_limit <= 25000.00)
);

CREATE INDEX mpay.ix_wallet_customer ON mpay.wallet (customer_id);
CREATE INDEX mpay.ix_wallet_account ON mpay.wallet (account_id);
CREATE INDEX mpay.ix_wallet_status ON mpay.wallet (wallet_status, customer_id);

The payment service schema uses BIGINT for the payment_id because projected volume (84 million payments in year one, growing 50% annually) will approach INTEGER limits within five years:

-- Payment Service Schema
CREATE TABLE mpay.payment (
    payment_id      BIGINT         NOT NULL GENERATED ALWAYS AS IDENTITY
                                    (START WITH 1, INCREMENT BY 1, CACHE 100),
    from_wallet_id  BIGINT         NOT NULL,
    to_wallet_id    BIGINT         NOT NULL,
    payment_amount  DECIMAL(10,2)  NOT NULL,
    currency_code   CHAR(3)        NOT NULL DEFAULT 'USD',
    payment_type    CHAR(3)        NOT NULL
                                    CONSTRAINT chk_pay_type
                                    CHECK (payment_type IN ('P2P','MER','BIL','REF')),
    payment_status  CHAR(1)        NOT NULL DEFAULT 'P'
                                    CONSTRAINT chk_pay_status
                                    CHECK (payment_status IN ('P','C','F','R','X')),
    payment_note    VARCHAR(500),
    idempotency_key VARCHAR(64)    NOT NULL,
    initiated_at    TIMESTAMP      NOT NULL DEFAULT CURRENT TIMESTAMP,
    completed_at    TIMESTAMP,
    CONSTRAINT pk_payment PRIMARY KEY (payment_id),
    CONSTRAINT fk_pay_from FOREIGN KEY (from_wallet_id)
        REFERENCES mpay.wallet (wallet_id) ON DELETE RESTRICT,
    CONSTRAINT fk_pay_to FOREIGN KEY (to_wallet_id)
        REFERENCES mpay.wallet (wallet_id) ON DELETE RESTRICT,
    CONSTRAINT chk_pay_amount CHECK (payment_amount > 0),
    CONSTRAINT chk_pay_wallets CHECK (from_wallet_id <> to_wallet_id),
    CONSTRAINT uq_pay_idempotency UNIQUE (idempotency_key)
);

CREATE INDEX mpay.ix_payment_from ON mpay.payment (from_wallet_id, initiated_at DESC);
CREATE INDEX mpay.ix_payment_to ON mpay.payment (to_wallet_id, initiated_at DESC);
CREATE INDEX mpay.ix_payment_status ON mpay.payment (payment_status)
    WHERE payment_status = 'P';

Key Design Decisions

Idempotency Key

The team added an idempotency_key column with a UNIQUE constraint. In a microservices architecture, network failures can cause payment requests to be retried. The idempotency key (a UUID generated by the client) ensures that a duplicate request produces the same result rather than creating a duplicate payment. This constraint prevents double-charges — a critical correctness requirement.

Cross-Schema Foreign Keys

The wallet table references meridian.customer and meridian.account — tables in the core banking schema. This cross-schema foreign key relationship ensures that wallets can only be created for valid customers with valid accounts. The team debated whether to enforce this at the database level (foreign key) or the application level (API validation). They chose database-level enforcement because a wallet linked to a nonexistent account is a data integrity catastrophe that no amount of application testing can guarantee against.

BIGINT for Payment ID

Marcus initially pushed back on BIGINT: "We only expect 84 million payments in year one — INTEGER handles 2.1 billion." Ramanathan's response: "With a CACHE of 100 and potential rollbacks, gaps accumulate. If the product succeeds and we reach 10 years of operation at 50% annual growth, we will be glad we chose BIGINT. The cost is 4 bytes per row. The cost of changing from INTEGER to BIGINT on a billion-row production table is a weekend of downtime."

Partial Index on Payment Status

The index on payment_status uses a WHERE clause to create a partial index (LUW 10.5+) that only indexes pending payments. Since 99% of payments are completed within minutes, the pending payment index remains small and efficient for the real-time status-check queries.

Outcome

The redesigned schema passed the DBA review. After deployment, the team observed:

• Zero data integrity violations in the first three months of operation
• The idempotency key prevented 47 duplicate payments during a network incident in week two
• Query response times consistently under 5 milliseconds for wallet balance and payment status lookups, thanks to appropriate indexing
• The DECIMAL(15,2) balance columns matched the core banking ledger to the penny in every reconciliation run

Marcus Chen later told Ramanathan: "I have been writing schemas for five years, and this is the first time I spent a full day on the DDL before writing any application code. I cannot believe how many issues we caught before they became production bugs."

Discussion Questions

1. Why did the team choose ON DELETE RESTRICT for all foreign keys rather than CASCADE? Under what circumstances might CASCADE be appropriate in a payment system?

2. The notification service schema (not shown) stores 15 million rows per month. Should the notification_id column use INTEGER or BIGINT? What factors inform your decision?

3. If Meridian Bank later decides to run MeridianPay on z/OS alongside the core banking system, what DDL changes would be needed? Consider tablespace creation, BOOLEAN conversion, and the ROW CHANGE TIMESTAMP feature.

4. The team used separate schemas (mpay vs. meridian) for the wallet and core banking tables. What are the advantages and disadvantages of this approach compared to using a single schema?

5. Marcus's original schema had no NOT NULL constraints. In a microservices environment where the application layer validates all inputs, what additional protection do database-level NOT NULL constraints provide?

Return to Chapter 11 | Continue to Case Study 2