Table ib_admin {

admin_id int [pk, increment]

name varchar(200)
email varchar(200)
number varchar(50)
password varchar(255)
profile_pic varchar(200)
status enum
}

Table ib_staff {
staff_id int [pk, increment]
name varchar(200)
staff_number varchar(100)
phone varchar(20)
email varchar(200)
password varchar(255)
sex enum
profile_pic varchar(200)
created_by_admin_id int
status enum
}

Table ib_clients {
client_id int [pk, increment]
name varchar(200)
national_id varchar(100)
phone varchar(20)
address varchar(200)
email varchar(200)
password varchar(255)
profile_pic varchar(200)
client_number varchar(100)
staff_id int
created_by_admin_id int
status enum
}

Table ib_roles {
role_id int [pk, increment]
 name varchar(100)
description varchar(255)
}

Table ib_permissions {
permission_id int [pk, increment]
name varchar(100)
description varchar(255)
}

Table ib_role_permissions {
role_id int
permission_id int
}

Table ib_user_roles {
user_role_id int [pk, increment]
admin_id int
staff_id int
role_id int
}

Table ib_acc_types {
acctype_id int [pk, increment]
name varchar(100)
description text
rate decimal
code varchar(50)
}

Table ib_bankaccounts {
account_id int [pk, increment]
acc_name varchar(200)
account_number varchar(50)
acc_type_id int
acc_status enum
acc_balance decimal
client_id int
staff_id int
created_by_admin_id int
 created_at timestamp
}

Table ib_transactions {
tr_id int [pk, increment]
tr_code varchar(100)
account_id int
tr_type enum
tr_status enum
transaction_amt decimal
client_id int
approved_by_admin_id int
created_by_staff_id int
created_at timestamp
}

Table ib_notifications {
notification_id int [pk, increment]
notification_details text
receiver_client_id int
receiver_staff_id int
created_by_admin_id int
created_at timestamp
}

Table ib_systemsettings {
id int [pk, increment]
sys_name text
sys_tagline text
sys_logo varchar(200)
updated_by_admin_id int
updated_at timestamp
}

Table ib_audit_logs {
audit_id int [pk, increment]
actor_admin_id int
actor_staff_id int
actor_client_id int
action varchar(200)
 table_name varchar(100)
record_id int
action_time timestamp
ip_address varchar(50)
user_agent varchar(255)
}

Ref: ib_staff.created_by_admin_id > ib_admin.admin_id

Ref: ib_clients.staff_id > ib_staff.staff_id
Ref: ib_clients.created_by_admin_id > ib_admin.admin_id
Ref: ib_bankaccounts.client_id > ib_clients.client_id
Ref: ib_bankaccounts.staff_id > ib_staff.staff_id
Ref: ib_bankaccounts.acc_type_id > ib_acc_types.acctype_id
Ref: ib_bankaccounts.created_by_admin_id > ib_admin.admin_id
Ref: ib_transactions.account_id > ib_bankaccounts.account_id
Ref: ib_transactions.client_id > ib_clients.client_id
Ref: ib_transactions.approved_by_admin_id > ib_admin.admin_id
Ref: ib_transactions.created_by_staff_id > ib_staff.staff_id
Ref: ib_notifications.receiver_client_id > ib_clients.client_id
Ref: ib_notifications.receiver_staff_id > ib_staff.staff_id
Ref: ib_notifications.created_by_admin_id > ib_admin.admin_id
Ref: ib_systemsettings.updated_by_admin_id > ib_admin.admin_id
Ref: ib_audit_logs.actor_admin_id > ib_admin.admin_id
Ref: ib_audit_logs.actor_staff_id > ib_staff.staff_id
Ref: ib_audit_logs.actor_client_id > ib_clients.client_id
Ref: ib_role_permissions.role_id > ib_roles.role_id
Ref: ib_role_permissions.permission_id > ib_permissions.permission_id
Ref: ib_user_roles.role_id > ib_roles.role_id
Ref: ib_user_roles.admin_id > ib_admin.admin_id
Ref: ib_user_roles.staff_id > ib_staff.staff_id
 Core Tables:
• Admin
• Staff
• Clients
• Accounts
• Transactions
• Notifications
• SystemSettings
• Account Types
• Roles
• Permissions
• Role_Permissions
• User_Roles (Admin/Staff)
• Audit Logs

স্ট্যান্ডার্ড বযাাংক াং ERD স্ট্রা চার

Roles:
• Admin = Full system controller
• Staff = Client/Account এর সাথে assigned োথে (Ex: KYC, support)
• Clients = Individual bank customers
• Accounts = Linked to client
• Transactions = Linked to account + client + optionally approved by admin
• Notifications = Sent by admin to staff/client

Admin Control
Standard:
Real-world banking system এ Admin (usually branch manager / head office super admin) সব
sensitive entity control েরথে পাথর:
• Staff hire / remove
• Clients verify / blacklist
• Accounts approve / close
• Transactions approve (above threshold)
 • System settings change
ERD:
➜ Admin all critical FK:
• ib_staff.created_by_admin_id
• ib_clients.created_by_admin_id
• ib_bankaccounts.created_by_admin_id
• ib_transactions.approved_by_admin_id
• ib_notifications.created_by_admin_id
• ib_systemsettings.updated_by_admin_id

Staff Hierarchy
Standard:
• Staff োথে Client servicing এর জন্য: KYC, onboarding, issue resolve.
• Staff এবং Admin clear separation োথে.
• Staff কে Admin create/assign েথর.

• ERD:
➜ ib_staff → created_by_admin_id
➜ ib_clients.staff_id → কোন্ staff এই client handle েরথে।

Account Linking

Standard:
• Account must link to one client.
• Account must link to account type.
• Staff responsible কে assign েরা যায়।
ERD:
➜ ib_bankaccounts.client_id
➜ ib_bankaccounts.acc_type_id
➜ ib_bankaccounts.staff_id

Transaction Audit Trail

Standard:
• Each transaction belongs to one account.
• Linked to client (owner).
• High-value or suspicious transactions must be approved_by_admin.
 • Optionally: Staff created it.
ERD:
➜ ib_transactions.account_id
➜ ib_transactions.client_id
➜ ib_transactions.approved_by_admin_id
➜ ib_transactions.created_by_staff_id
Fully traceable — standard KYC, AML (Anti Money Laundering) guideline aligned.

Notification Flow
Standard:
• Admin → Clients (alert, statements)
• Admin → Staff (internal instructions)
ERD:
➜ ib_notifications.receiver_client_id
➜ ib_notifications.receiver_staff_id
➜ ib_notifications.created_by_admin_id
Proper one-way controlled flow — no direct cross talk.

System Settings
Standard:
• System config only Admin can change.
• Example: interest rates, logos, system name.
ERD:
➜ ib_systemsettings.updated_by_admin_id

1. Admin Power & Control

Expected Business Logic:
• Admin কযন্ system setup/control/monitor েরথে পাথর
• Admin কযন্ Staff, Client, Account create/approve েরথে পাথর
• Admin যদি ন্া োথে, কেউই দেেু েরথে পারথব ন্া
ERD Implementation:
• Admin is creator of:
o Staff (created_by_admin_id)
o Client (created_by_admin_id)
o Account (created_by_admin_id)
• Admin approves transactions: approved_by_admin_id
 • Admin sends all notifications: created_by_admin_id
• Admin updates system settings: updated_by_admin_id
Confirmed: Admin has full centralized control —

2. Staff Hierarchy & Role

Expected Business Logic:
• Staff created by admin
• Staff assigned to clients
• Staff can initiate transactions
• Staff must follow RBAC rules
ERD Implementation:
• ib_staff → created_by_admin_id
• ib_clients.staff_id → maps assigned staff
• ib_transactions.created_by_staff_id
• ib_user_roles.staff_id for RBAC
Confirmed: Staff functions align with real operational flow —

3. Client Lifecycle
Expected Business Logic:
• Client has unique identity (NID/email)
• Linked to a staff
• Has one or more bank accounts
• Can perform transactions
• Can receive notifications
• Can be inactive/blacklisted
ERD Implementation:
• Unique: national_id, email, client_number
• Linked to: staff_id
• ib_bankaccounts.client_id
• ib_transactions.client_id
• ib_notifications.receiver_client_id
• Status: ENUM('active', 'inactive', 'blacklisted')
Confirmed: Covers onboarding, KYC, account, transaction, notification —

4. Bank Account Ownership

Expected Business Logic:
• Account is linked to 1 client, 1 type
• Created by Admin
• Assigned staff optional
• Tracks balance/status
 ERD Implementation:
• client_id (FK)
• acc_type_id → links to ib_acc_types
• staff_id (optional)
• created_by_admin_id
• acc_balance, acc_status (active/closed/suspended)
Confirmed: Proper account linkage and status —

5. Transaction Lifecycle
Expected Business Logic:
• Belongs to a client and account
• Can be created by staff
• Must be approved by admin (in case of manual verification)
• Has type, status, amount
ERD Implementation:
• account_id, client_id
• tr_type (enum: deposit, withdraw, transfer)
• tr_status (enum: pending, approved, rejected)
• approved_by_admin_id, created_by_staff_id
Confirmed: Clear flow of who initiates, who approves —

6. Role-Based Access Control (RBAC)

Expected Business Logic:
• Define roles (admin, teller, manager)
• Assign roles to users (admin/staff)
• Roles map to permissions
ERD Implementation:
• ib_roles, ib_permissions, ib_role_permissions
• ib_user_roles → links roles to admin_id or staff_id
Confirmed: Full RBAC structure present —

7. System Notifications
Expected Business Logic:
• Admin can send notices to clients/staff
• Each notification logged with timestamp
ERD Implementation:
• receiver_client_id, receiver_staff_id, created_by_admin_id
• created_at timestamp
• Confirmed: Directional and traceable —
8. System Settings + Logs
Expected Business Logic:
• System configuration only editable by admin
• Each sensitive operation logged for audit
ERD Implementation:
• ib_systemsettings.updated_by_admin_id
• ib_audit_logs: actor_admin_id, actor_staff_id, actor_client_id, table_name, action,
ip_address, user_agent
Confirmed: Covers compliance (ISO/PCI), audit trail —

Sensitive Field Security

• password fields = VARCHAR(255) → ready for bcrypt()
• transaction_amt = DECIMAL(15,2) → no string corruption
• audit_logs = full trail + environment info
Security concern addressed —

Final Verdict:
Module Remarks
Admin Control Full system-wide control with audit
Staff Management Properly assigned + limited access
Client Management Clean lifecycle + status control
Type-wise, owner-wise, status
Account Lifecycle
tracking
Staff→Admin flow, status, type
Transactions
tracked
Notifications Admin-to-users controlled channel
Role + permission based dynamic
RBAC System
access
Audit Logs Tracks action, by whom, on what
Fields designed for hashing +
Sensitive Data
compliance