This content originally appeared on DEV Community and was authored by Ankit Kumar
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!!!!!
This content originally appeared on DEV Community and was authored by Ankit Kumar
Ankit Kumar | Sciencx (2021-07-09T20:32:38+00:00) Data Modelling / Database Design. Retrieved from https://www.scien.cx/2021/07/09/data-modelling-database-design/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.