Skip to content

database-schema.md

Latest commit

 

History

History
107 lines (88 loc) · 4.61 KB

database-schema.md

File metadata and controls

107 lines (88 loc) · 4.61 KB

Banking System Database Schema

This document describes the relational schema that persists the banking domain. It covers the entities for accounts, transactions, and observers and lists the SQL migrations required to create the schema from scratch.

Entity overview

Accounts

Accounts represent customer-owned instruments such as savings, current, and fixed-deposit accounts. Each account captures the customer name, balance, type, and creation date.

Column Type Notes
account_number INTEGER Primary key generated by the application.
holder_name VARCHAR(120) Current owner of the account.
account_type VARCHAR(30) Canonical type label (savings, current, fixed).
balance DOUBLE PRECISION Current balance tracked in the domain.
creation_date VARCHAR(20) ISO-8601 date string (yyyy-MM-dd).

Transactions

Transactions capture the immutable history of credits and debits applied to an account. They are stored with the identifier generated by the domain model and preserve the timestamp and any related account reference for transfers.

Column Type Notes
transaction_id VARCHAR(64) Primary key copied from the domain entity.
account_number INTEGER Foreign key to accounts.account_number.
transaction_type VARCHAR(40) Concrete class name (DepositTransaction, etc.).
amount DOUBLE PRECISION Positive value for the transaction.
occurred_at TIMESTAMP When the transaction happened.
related_account INTEGER Nullable; set for transfer send/receive events.
details VARCHAR(255) Optional textual metadata (currently unused).

Observers

Observers represent configured notification hooks. While the current release ships two built-in observers (ConsoleNotifier, TransactionLogger), the table is designed to store additional observer registrations or remote listeners.

Column Type Notes
observer_id INTEGER Primary key, auto-increment/identity.
observer_name VARCHAR(120) Friendly name configured by operators.
observer_type VARCHAR(60) Implementation identifier/class name.
target VARCHAR(255) Endpoint or channel (e.g. email/webhook).
is_active BOOLEAN Flag to enable/disable the observer.
created_at TIMESTAMP Defaults to current timestamp.

Baseline migration

The following SQL can be executed to bootstrap a new database. The statements rely only on ANSI-compatible features so they work with popular engines (H2, PostgreSQL, MySQL, SQLite). Engines that require explicit identity/auto-increment syntax may need a small adjustment on the observer_id column.

CREATE TABLE IF NOT EXISTS accounts (
    account_number INTEGER PRIMARY KEY,
    holder_name VARCHAR(120) NOT NULL,
    account_type VARCHAR(30) NOT NULL,
    balance DOUBLE PRECISION NOT NULL,
    creation_date VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS transactions (
    transaction_id VARCHAR(64) PRIMARY KEY,
    account_number INTEGER NOT NULL,
    transaction_type VARCHAR(40) NOT NULL,
    amount DOUBLE PRECISION NOT NULL,
    occurred_at TIMESTAMP NOT NULL,
    related_account INTEGER,
    details VARCHAR(255),
    FOREIGN KEY (account_number) REFERENCES accounts(account_number)
);

CREATE TABLE IF NOT EXISTS observers (
    observer_id INTEGER PRIMARY KEY,
    observer_name VARCHAR(120) NOT NULL,
    observer_type VARCHAR(60) NOT NULL,
    target VARCHAR(255),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Migration workflow

  1. Load driver and configuration – Ensure the JDBC driver for the selected database is on the classpath and provide the URL, credentials, and driver class name via environment variables or configuration files.
  2. Run the baseline script – Execute the SQL statements above inside a single transaction so the schema creation is atomic.
  3. Record schema version – Track applied migrations (e.g. with a schema_version table or an external tool) to support incremental upgrades later.
  4. Seed observers (optional) – Insert default observers that mirror the existing console logger/notifier to keep behaviour consistent across restarts.
  5. Apply future migrations – Introduce new DDL scripts that transform the schema while preserving data. Keep them idempotent when possible.

The application automates steps 1–2 during startup and can be re-run safely; the CREATE TABLE IF NOT EXISTS guards avoid recreating objects that already exist.