Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Notifications You must be signed in to change notification settings

ankittkp/Bank-Database-Design

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Folders and files

Latest commit

History

Repository files navigation

Bank-Database-Design

📩 Latest Blog Posts : https://dev.to/jinxankit/data-modelling-database-design-4hhc

1. Create and manage accounts
2. Record all transactions history from accounts -> to accounts etc.
3. Perform money transfer between 2 accounts consistently within a transactions
4. Maintaining a Loan Records

Database Design

The basics of database design is 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.

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

How to use dbdigram 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 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" );

THANK YOU.