Data Modelling / Database Design

#database #mysql #postgres #design

Database design is structuring data and organised relationships in a database. It goes beyond how the database further works.
I was learning about cool stuff dbdigram.io for designing a database so hitting a random try for Bank Database design.

Requirements

Create and manage accounts { owner, branch, balance details...}
Record all transactions history from accounts -> to accounts etc.
Perform money transfer between 2 accounts consistently within a transactions
Maintaining a Loan Records

Database Design

The basics of database design are identifying what data needs to get stored, grouping such data into tables or collections and adding relationships between related data points. The whole database design process involves

Using Entity-Relationship(ER) diagrams to visualize the objects that get stored and their relationships.

Let's see how this can be designed on diagrams using dbdigram.io and then converted to SQL and PostgreSQL.

Exciting right?

How to use DB diagram
The tool is simple: you write code, it renders the ER diagram 👌. You can then export to PDF, PNG or generate SQL code with it ⭐️.

It uses DBML (database markup language) to define and document database schemas.

customer_id is the primary key(pk keyword) and auto-incremented
created_at enclose timezone information as well
deleted_at for maintaining the deletion record

bigserial in PostgreSQL is basically a big autoincrementing integers (8-byte/64-bit)
customer_id int [ref: > C.customer_id], customer_id is foreign key user here

Hence the Final ER - Diagram Result:

Let's write/generate Mysql Code for this schema now

CREATE DATABASE bank;
USE bank;

CREATE TABLE customers (
customer_id int PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR,
last_name VARCHAR,
city VARCHAR,
mobile_no VARCHAR,
pancard_no VARCHAR,
dob VARCHAR,
created_at timestampz DEFAULT "now()",
deleted_at timestampz
);

CREATE TABLE branchs (
branch_id int PRIMARY KEY AUTO_INCREMENT,
branch_name VARCHAR,
branch_location VARCHAR,
created_at timestampz DEFAULT "now()",
deleted_at timestampz
);

CREATE TABLE accounts (
account_id bigserial PRIMARY KEY,
customer_id int,
balance bigint,
account_status VARCHAR,
account_type VARCHAR,
currency VARCHAR,
created_at timestampz DEFAULT "now()",
deleted_at timestampz
);

CREATE TABLE transactions (
transaction_id bigserial PRIMARY KEY,
transaction_type VARCHAR,
from_account_id bigint,
to_account_id bigint,
date_issued date,
amount bigint,
transaction_medium VARCHAR,
created_at timestampz DEFAULT "now()",
deleted_at timestampz
);

CREATE TABLE loans (
loan_id bigserial PRIMARY KEY,
customer_id int,
branch_id int,
loan_amount bigint,
date_issued date,
created_at timestampz DEFAULT "now()",
deleted_at timestampz
);

ALTER TABLE accounts ADD FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

ALTER TABLE transactions ADD FOREIGN KEY (from_account_id) REFERENCES accounts (account_id);

ALTER TABLE transactions ADD FOREIGN KEY (to_account_id) REFERENCES accounts (account_id);

ALTER TABLE loans ADD FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

ALTER TABLE loans ADD FOREIGN KEY (branch_id) REFERENCES branchs (branch_id);

PostgreSQL Code

CREATE TABLE "customers" (
"customer_id" SERIAL PRIMARY KEY,
"first_name" VARCHAR,
"last_name" VARCHAR,
"city" VARCHAR,
"mobile_no" VARCHAR,
"pancard_no" VARCHAR,
"dob" VARCHAR,
"created_at" timestampz DEFAULT 'now()',
"deleted_at" timestampz
);

CREATE TABLE "branchs" (
"branch_id" SERIAL PRIMARY KEY,
"branch_name" VARCHAR,
"branch_location" VARCHAR,
"created_at" timestampz DEFAULT 'now()',
"deleted_at" timestampz
);

CREATE TABLE "accounts" (
"account_id" bigserial PRIMARY KEY,
"customer_id" int,
"balance" bigint,
"account_status" VARCHAR,
"account_type" VARCHAR,
"currency" VARCHAR,
"created_at" timestampz DEFAULT 'now()',
"deleted_at" timestampz
);

CREATE TABLE "transactions" (
"transaction_id" bigserial PRIMARY KEY,
"transaction_type" VARCHAR,
"from_account_id" bigint,
"to_account_id" bigint,
"date_issued" date,
"amount" bigint,
"transaction_medium" VARCHAR,
"created_at" timestampz DEFAULT 'now()',
"deleted_at" timestampz
);

CREATE TABLE "loans" (
"loan_id" bigserial PRIMARY KEY,
"customer_id" int,
"branch_id" int,
"loan_amount" bigint,
"date_issued" date,
"created_at" timestampz DEFAULT 'now()',
"deleted_at" timestampz
);