# Pattern: Master & Junction Tables (Backend)

Panduan konsisten untuk pembuatan master data dan junction table di sistem ini.

---

## 1. Penamaan Tabel (Table Naming)

**Aturan:** Lowercase, **tanpa underscore**, plural (akhiran `s`).

| ❌ Jangan           | ✅ Gunakan        |
|--------------------|-------------------|
| `education_levels` | `educationlevels` |
| `placement_areas`  | `placementareas`  |
| `email_groups`     | `emailgroups`     |
| `email_group_member` | `emailgroupmembers` |
| `blocked_ips`      | `blockedips`      |
| `work_activity_item` | `workactivityitems` |
| `workactivityitem_files` | `workactivityitemfiles` |
| `connection_attempts` | `connectionattempts` |

**Contoh lain:**  
`shifttypes`, `genders`, `bankaccounts`, `designations`, `leaveapplications`, `holidaylists`, `tenderquotations`, `budgetplanitems`.

---

## 2. Kolom Timestamp Default

Setiap tabel master/junction wajib punya:

- **creation** – `DATETIME NOT NULL` (tetap dipakai untuk audit)
- **modified** – `DATETIME NOT NULL`
- **modified_by** – `INT(11) NOT NULL` (FK ke users)
- **owner** – `INT(11) NOT NULL` (FK ke users)
- **createdAt** – `DATETIME NULL` (default system)
- **updatedAt** – `DATETIME NULL` (default system)

`creation`/`modified` tetap dipakai di logic (create/update). `createdAt`/`updatedAt` untuk standar sistem dan integrasi.

---

## 3. Penamaan Index

Mengikuti nama tabel (lowercase, no underscore):

- Tabel `educationlevels` → `idx_educationlevels_company_id`, `idx_educationlevels_enabled`, dll.
- Tabel `placementareas` → `idx_placementareas_company_id`, `idx_placementareas_parent_id`, dll.

---

## 4. Struktur Folder & File Master

```
masters/
  <entityPlural>/           # camelCase folder, e.g. educationLevels, placementAreas
    <entity>.model.js       # e.g. educationLevel.model.js
    <entity>.service.js     # e.g. educationLevel.service.js
    <entity>s.controller.js # e.g. educationLevels.controller.js
```

- **Model:** `tableName: "educationlevels"`, `freezeTableName: true`, definisi `createdAt`/`updatedAt` dengan `field: "createdAt"` / `field: "updatedAt"` jika nama kolom di DB persis begitu.

---

## 5. Model (Sequelize) – Ringkas

```javascript
{
  tableName: "educationlevels",   // lowercase, no underscore, plural
  freezeTableName: true,
  indexes: [
    { name: "idx_educationlevels_company_id", fields: ["company_id"] },
    { name: "idx_educationlevels_enabled", fields: ["enabled"] },
  ],
}
// Kolom: id, creation, modified, modified_by, owner, createdAt, updatedAt, enabled, name, display_name, company_id, ...
```

---

## 6. Junction / Tabel Relasi

- Nama tabel: lowercase, no underscore, plural.  
  Contoh: `emailgroupmembers`, `workactivityitems`, `budgetplanitems`.
- Tetap pakai kolom `creation`, `modified`, `modified_by`, `owner` dan (bila sudah standar) `createdAt`, `updatedAt`.

---

## 7. Migration SQL (phpMyAdmin)

- Buat tabel dengan nama yang sudah mengikuti pattern (e.g. `educationlevels`).
- Sertakan `createdAt`, `updatedAt` sejak awal, atau tambah lewat `ALTER TABLE ... ADD COLUMN`.
- Constraint FK: nama constraint boleh deskriptif (e.g. `fk_education_levels_company`), yang penting `REFERENCES` mengarah ke nama tabel yang benar (e.g. `educationlevels`).

---

## 8. Checklist Modul Baru

- [ ] Nama tabel: lowercase, no underscore, plural.
- [ ] Kolom: creation, modified, modified_by, owner, createdAt, updatedAt, enabled (jika dipakai).
- [ ] Model: `tableName` dan `indexes` sesuai pattern.
- [ ] Migration: nama tabel & index konsisten, FK ke tabel dengan nama pattern yang benar.
