My-KOPKB/docs/db_design.md

🧾 3. Table Design (Detailed)

---

loan_applications (MAIN TABLE)

This is the core record.

id (PK)
loan_type_id (FK)
amount_requested
deduction_period
purpose

status (SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED)
created_at
updated_at

applicants

One-to-one with loan application.

id (PK)
loan_application_id (FK)

name
ic_num
phone_num
member_id_num
birth_date
email

employer_name
current_position
position_status

address

bank_account_number
bank_name

basic_salary
total_income
deduction

service_date
service_period

guarantors

👉 One loan → 3 guarantors (One-to-Many)

id (PK)
loan_application_id (FK)

name
ic_num
unit
address
phone_num
member_id_number

basic_salary
allowance
total_income

loan_approvals

One-to-one with loan application.

id (PK)
loan_application_id (FK)

level
role
approver_id

decision (PENDING, APPROVED, REJECTED)
remarks

approved_at

loan_evaluations

id (PK)
loan_application_id (FK)

calculated_by (FK users)

total_loan
total_payment
period

insurance_per_month

deduction_start_date
deduction_end_date

number_of_shares
total_fees
debt_balance

payment_date

created_at

documents

id
loan_application_id (FK)

file_name
file_url
file_type

uploaded_by
uploaded_at

loan_status_history

id (PK)
loan_application_id (FK)

status (DRAFT, SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED)

changed_by (FK users)
changed_at (timestamp)

remarks (optional)

repayments

id (PK)
loan_application_id (FK)

installment_number (1, 2, 3...)

due_date
amount_due

amount_paid
payment_date

status (PENDING, PAID, LATE)

created_at

---

🔗 4. Relationships Diagram (Simple)

loan_applications
    │
    ├── applicants (1:1)
    ├── guarantors (1:N)
    └── loan_reviews (1:1)

---

🧩 6. JPA Entity Mapping (Example)

LoanApplication

@Entity
public class LoanApplication {
    @Id
    @GeneratedValue
    private Long id;

    private BigDecimal amountRequested;
    private Integer deductionPeriod;
    private String purpose;

    @Enumerated(EnumType.STRING)
    private LoanStatus status;

    @OneToOne(mappedBy = "loanApplication", cascade = CascadeType.ALL)
    private Applicant applicant;

    @OneToMany(mappedBy = "loanApplication", cascade = CascadeType.ALL)
    private List<Guarantor> guarantors;

    @OneToOne(mappedBy = "loanApplication", cascade = CascadeType.ALL)
    private LoanReview review;
}

---

⚠️ 7. Important Constraints

🔹 Enforce in Backend (NOT DB)

• Exactly 3 guarantors
• Valid IC format
• Salary > 0
• Loan amount limits

---

🔐 8. Audit & Compliance (Very Important)

Add these fields to key tables:

created_by
created_at
updated_by
updated_at

---

🚀 9. Future Enhancements

You can easily extend this design with:

• 📎 documents table (for uploads)
• 📊 repayments table
• 🧾 audit_logs
• 🔄 loan_status_history

---

🏁 Final Architecture Mapping

Your DB aligns perfectly with your system:

Frontend Form → LoanApplication → Applicant + Guarantors
                                      ↓
                                Officer Review
                                      ↓
                                   Decision