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
);
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"
);
References:
https://github.com/holistics/dbml
Get the code here
https://github.com/jinxankit/Bank-DataBase-Design
THANK YOU!!!!!
Top comments (1)
👍