Banking Database Schema

Tables in this schema

Schema (SQL)

MySQL-dialect DDL. Open it in the editor for a visual ER diagram you can edit, or copy the SQL straight into your project.

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(254) NOT NULL UNIQUE,
  phone VARCHAR(32),
  created_at DATETIME NOT NULL
);

CREATE TABLE accounts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  account_number VARCHAR(34) NOT NULL UNIQUE,
  type VARCHAR(20) NOT NULL,
  balance DECIMAL(16,2) NOT NULL DEFAULT 0,
  currency VARCHAR(3) NOT NULL,
  opened_at DATETIME NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE transactions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  account_id INT NOT NULL,
  amount DECIMAL(16,2) NOT NULL,
  direction VARCHAR(6) NOT NULL,
  description VARCHAR(200),
  created_at DATETIME NOT NULL,
  FOREIGN KEY (account_id) REFERENCES accounts(id)
);

CREATE TABLE cards (
  id INT PRIMARY KEY AUTO_INCREMENT,
  account_id INT NOT NULL,
  card_number VARCHAR(19) NOT NULL UNIQUE,
  type VARCHAR(20) NOT NULL,
  expires_on DATE NOT NULL,
  status VARCHAR(20) NOT NULL,
  FOREIGN KEY (account_id) REFERENCES accounts(id)
);

CREATE TABLE loans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  principal DECIMAL(16,2) NOT NULL,
  interest_rate DECIMAL(5,2) NOT NULL,
  term_months INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  opened_at DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);