ISMAIL MASSERAN
249 lines
3.3 KiB
Markdown
249 lines
3.3 KiB
Markdown
# 🧾 3. Table Design (Detailed)
|
|
|
|
---
|
|
|
|
## `loan_applications` (MAIN TABLE)
|
|
This is the core record.
|
|
|
|
```sql
|
|
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.
|
|
|
|
```sql
|
|
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)
|
|
|
|
```sql
|
|
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.
|
|
|
|
```sql
|
|
id (PK)
|
|
loan_application_id (FK)
|
|
|
|
level
|
|
role
|
|
approver_id
|
|
|
|
decision (PENDING, APPROVED, REJECTED)
|
|
remarks
|
|
|
|
approved_at
|
|
```
|
|
|
|
## `loan_evaluations`
|
|
```sql
|
|
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`
|
|
```sql
|
|
id
|
|
loan_application_id (FK)
|
|
|
|
file_name
|
|
file_url
|
|
file_type
|
|
|
|
uploaded_by
|
|
uploaded_at
|
|
```
|
|
|
|
## `loan_status_history`
|
|
```sql
|
|
id (PK)
|
|
loan_application_id (FK)
|
|
|
|
status (DRAFT, SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED)
|
|
|
|
changed_by (FK users)
|
|
changed_at (timestamp)
|
|
|
|
remarks (optional)
|
|
```
|
|
|
|
## `repayments`
|
|
```sql
|
|
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)
|
|
|
|
```id="c2x7qp"
|
|
loan_applications
|
|
│
|
|
├── applicants (1:1)
|
|
├── guarantors (1:N)
|
|
└── loan_reviews (1:1)
|
|
```
|
|
|
|
---
|
|
|
|
|
|
# 🧩 6. JPA Entity Mapping (Example)
|
|
|
|
### LoanApplication
|
|
|
|
```java
|
|
@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:
|
|
|
|
```sql
|
|
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:
|
|
|
|
```id="z7wq3f"
|
|
Frontend Form → LoanApplication → Applicant + Guarantors
|
|
↓
|
|
Officer Review
|
|
↓
|
|
Decision
|
|
```
|
|
|