# STRUKTUR DATABASE FLS3N 2025 - REFERENSI TEKNIS

## Diagram Entity Relationship

```
┌─────────────────────┐
│  peserta_terdaftar  │  (Master Data - Validasi)
├─────────────────────┤
│ id (PK)             │
│ Nama_Peserta (U)    │ ◄──┐
│ NPSN (U)            │    │ Composite Unique
│ domisili            │ ◄──┘ (Nama_Peserta, NPSN, domisili)
│ created_at          │
└──────┬──────────────┘
       │ Validation Check
       │
       ├──────────────────────────────────────────────────┐
       │                                                  │
       ▼                                                  ▼
┌──────────────────┐                          ┌──────────────────────┐
│ domisilikota     │                          │ domisilikabupaten    │
│ (Kota Salatiga)  │                          │ (Kab. Semarang)      │
├──────────────────┤                          ├──────────────────────┤
│ id (PK)          │                          │ id (PK)              │
│ NISN (U)         │ ◄────────┐               │ NISN (U)             │ ◄────────┐
│ Nama_Peserta     │          │               │ Nama_Peserta        │          │
│ Nama_Sekolah     │          │ Duplicate     │ Nama_Sekolah        │          │ (per domisili)
│ NPSN             │          │ Check         │ NPSN                 │          │
│ TTL              │          │               │ TTL                  │          │
│ Email            │          │               │ Email                │          │
│ Jenis_Kelamin    │          │               │ Jenis_Kelamin        │          │
│ Foto_Peserta     │          │               │ Foto_Peserta         │          │
│ Karya            │          │               │ Karya                │          │
│ ID_Cabang_Lomba (FK) ┐      │               │ ID_Cabang_Lomba (FK) │          │
│ created_at       │  │       │               │ created_at           │          │
└──────────────────┘  │       │               └──────────────────────┘          │
                      │       │                                                 │
                      │       └─────────────────────────────────────────────────┘
                      │
                      └──────────────────────────────────────────────────────────┐
                                                                                 │
┌──────────────────────┐                                                        │
│ domisilikabupaten2   │ ◄────────────────────────────────────────────────────┐─┤
│ (Kab. Demak)         │                                                       │ │
├──────────────────────┤                                                       │ │
│ id (PK)              │                                                       │ │
│ NISN (U)             │ (Structure identik)                                   │ │
│ ... (same columns)   │                                                       │ │
│ created_at           │                                                       │ │
└──────────────────────┘                                                       │ │
                                                                               │ │
                                                                               │ │
┌──────────────────┐                                                           │ │
│  cabang_lomba    │ ◄──────────────────────────────────────────────────────┬─┘ │
├──────────────────┤                                                        │    │
│ ID (PK)          │                                                        │    │
│ Bidang_Lomba     │                                                        │    │
│ ...              │                                                        │    │
└──────────────────┘                                                        │    │
                                                                            │    │
                                                                            │────┘
                                                                            │
                                                    ┌───────────────────────┘
                                                    │
                                                    ▼
┌──────────────────┐    ┌──────────────────┐    ┌──────────────────┐
│   status_lomba   │    │     users        │    │  cabang_lomba    │
├──────────────────┤    ├──────────────────┤    ├──────────────────┤
│ id               │    │ id (PK)          │    │ ID (PK)          │
│ name (PK)        │    │ nama_lengkap     │    │ Bidang_Lomba     │
│ status           │    │ role (admin/juri)│    │ Deskripsi        │
│ updated_at       │    │ domisili         │    │ created_at       │
│ updated_by (FK)  │───→│ level            │    └──────────────────┘
└──────────────────┘    │ ...              │
                        └──────────────────┘
                        (Users: Admin & Juri)
```

---

## Detail Tabel

### 1. peserta_terdaftar (Master Validation)

```sql
CREATE TABLE peserta_terdaftar (
    id INT PRIMARY KEY AUTO_INCREMENT,
    Nama_Peserta VARCHAR(255) NOT NULL,
    NPSN VARCHAR(50) NOT NULL,
    domisili VARCHAR(50) NOT NULL CHECK(domisili IN ('kabupaten semarang', 'kabupaten demak', 'kota salatiga')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_peserta (Nama_Peserta, NPSN, domisili),
    INDEX idx_nama (Nama_Peserta),
    INDEX idx_npsn (NPSN),
    INDEX idx_domisili (domisili)
);
```

**Penggunaan:**
- Admin menambah/import data peserta
- Saat form pendaftaran, sistem cek: apakah nama_peserta + NPSN ada di tabel ini?
- Jika tidak ada → reject (error: "Data peserta tidak terdaftar")
- Jika ada → lanjut insert ke tabel domisili

**Record: ~1000-5000 entries** (Master list)

---

### 2. domisilikota (Peserta Registrasi - Kota Salatiga)

```sql
CREATE TABLE domisilikota (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Data Pribadi
    NISN VARCHAR(10) NOT NULL,
    Nama_Peserta VARCHAR(255) NOT NULL,
    TTL VARCHAR(255) NOT NULL,  -- "Jakarta, 31 Desember 2000"
    Email VARCHAR(255) NOT NULL,  -- "email@mail.com – 0812345678"
    Jenis_Kelamin VARCHAR(20),  -- "Laki-laki" / "Perempuan"
    
    -- Data Sekolah
    Nama_Sekolah VARCHAR(255) NOT NULL,
    NPSN VARCHAR(50) NOT NULL,
    
    -- Dokumen/Karya
    Foto_Peserta LONGTEXT NOT NULL,  -- URL link
    Karya LONGTEXT NOT NULL,  -- URL link
    
    -- Relasi
    ID_Cabang_Lomba INT NOT NULL,  -- FK ke cabang_lomba.ID
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_nisn (NISN),
    UNIQUE KEY unique_pendaftaran (NISN, ID_Cabang_Lomba),
    INDEX idx_peserta (Nama_Peserta),
    INDEX idx_cabang (ID_Cabang_Lomba),
    FOREIGN KEY (ID_Cabang_Lomba) REFERENCES cabang_lomba(ID)
);
```

**Record: ~500-1000 entries** (Active registrants)

---

### 3. domisilikabupaten (Peserta Registrasi - Kabupaten Semarang)

```sql
-- Struktur IDENTIK dengan domisilikota
-- Hanya berbeda isi (peserta dari kabupaten semarang)
CREATE TABLE domisilikabupaten (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NISN VARCHAR(10) NOT NULL UNIQUE,
    Nama_Peserta VARCHAR(255),
    TTL VARCHAR(255),
    Email VARCHAR(255),
    Jenis_Kelamin VARCHAR(20),
    Nama_Sekolah VARCHAR(255),
    NPSN VARCHAR(50),
    Foto_Peserta LONGTEXT,
    Karya LONGTEXT,
    ID_Cabang_Lomba INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (ID_Cabang_Lomba) REFERENCES cabang_lomba(ID)
);
```

---

### 4. domisilikabupaten2 (Peserta Registrasi - Kabupaten Demak)

```sql
-- Struktur IDENTIK dengan domisilikota
-- Hanya berbeda isi (peserta dari kabupaten demak)
CREATE TABLE domisilikabupaten2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NISN VARCHAR(10) NOT NULL UNIQUE,
    Nama_Peserta VARCHAR(255),
    -- ... sama seperti domisilikota
);
```

---

### 5. status_lomba (Status Registrasi)

```sql
CREATE TABLE status_lomba (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,  -- 'kota_salatiga', 'kabupaten_semarang', 'kabupaten_demak'
    status ENUM('open', 'closed') DEFAULT 'open',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT,  -- FK ke users.id
    
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- Sample Data:
-- INSERT INTO status_lomba (name, status) VALUES 
-- ('kota_salatiga', 'open'),
-- ('kabupaten_semarang', 'open'),
-- ('kabupaten_demak', 'closed');
```

**Penggunaan:**
- Kontrol pembukaan/penutupan registrasi per domisili
- Sebelum form ditampilkan, check status di tabel ini
- Jika status = 'closed' → form tidak bisa diakses

---

### 6. cabang_lomba (Master Kategori Lomba)

```sql
CREATE TABLE cabang_lomba (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Bidang_Lomba VARCHAR(255) NOT NULL,  -- "Puisi", "Cerpen", "Esai", dll
    Deskripsi TEXT,
    Kategori VARCHAR(100),  -- "Bahasa Indonesia", "Bahasa Inggris", dll
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_bidang (Bidang_Lomba),
    INDEX idx_kategori (Kategori)
);

-- Sample Data:
-- INSERT INTO cabang_lomba (Bidang_Lomba, Kategori) VALUES 
-- ('Puisi Bahasa Indonesia', 'Sastra'),
-- ('Cerpen Bahasa Inggris', 'Sastra'),
-- ('Esai', 'Sastra');
```

---

### 7. users (Admin & Juri)

```sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama_lengkap VARCHAR(255) NOT NULL,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL (hashed),
    email VARCHAR(255) UNIQUE,
    role ENUM('admin', 'juri') DEFAULT 'juri',
    domisili VARCHAR(100),  -- Hanya berlaku untuk juri (untuk filter data)
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_role (role),
    INDEX idx_domisili (domisili)
);

-- Admin: can access all
-- Juri: only can access their domisili data
```

---

## Query Patterns

### Pattern 1: Validasi Saat Registrasi

```sql
-- Step 1: Check master peserta
SELECT id FROM peserta_terdaftar 
WHERE Nama_Peserta = 'SISWA SATU' 
AND NPSN = '12345678' 
AND domisili = 'kabupaten semarang'
LIMIT 1;

-- If no result → Error!
-- If found → Continue

-- Step 2: Check NISN not duplicate
SELECT NISN FROM domisilikabupaten 
WHERE NISN = '9876543210'
LIMIT 1;

-- If found → Error: "NISN sudah terdaftar"
-- If not found → Continue to INSERT
```

### Pattern 2: Display Peserta Terdaftar (Admin)

```sql
-- With pagination
SELECT id, Nama_Peserta, NPSN, domisili, created_at 
FROM peserta_terdaftar 
WHERE domisili LIKE ?  -- Filter domisili
AND (Nama_Peserta LIKE ? OR NPSN LIKE ?)  -- Search
ORDER BY created_at ASC 
LIMIT ?, ?;  -- Pagination
```

### Pattern 3: Count Peserta per Cabang & Domisili (Admin Dashboard)

```sql
-- Kota Salatiga - Puisi
SELECT COUNT(*) as total 
FROM domisilikota 
WHERE ID_Cabang_Lomba = 1;

-- Kabupaten Semarang - Cerpen
SELECT COUNT(*) as total 
FROM domisilikabupaten 
WHERE ID_Cabang_Lomba = 2;

-- Gabung semua
SELECT 
    (SELECT COUNT(*) FROM domisilikota WHERE ID_Cabang_Lomba = ?) as kota_salatiga,
    (SELECT COUNT(*) FROM domisilikabupaten WHERE ID_Cabang_Lomba = ?) as kab_semarang,
    (SELECT COUNT(*) FROM domisilikabupaten2 WHERE ID_Cabang_Lomba = ?) as kab_demak;
```

### Pattern 4: Cek Status Registrasi

```sql
-- Check apakah registrasi dibuka
SELECT status FROM status_lomba 
WHERE name = 'kota_salatiga';  

-- Result: 'open' atau 'closed'
-- If 'closed' → Show "Pendaftaran ditutup" message
```

---

## Observations & Insights

### ✓ Yang Baik:
1. **Separation of Concerns**: Master data terpisah dari registrant data
2. **Domisili Separation**: Peserta dipisah per lokasi (mudah delegasi ke juri lokal)
3. **Validation Layer**: Double check (master + duplicate NISN)
4. **Flexibility**: Bisa handle PKBM (NPSN = "PKBM")
5. **Audit Trail**: `created_at`, `updated_at` timestamp

### ⚠ Issues/Improvements:
1. **No School Master Table**: Nama sekolah bisa duplikat/typo
   - Solusi: Buat `master_sekolah` dengan UNIQUE constraint
   
2. **No Edit Peserta**: Hanya bisa delete, tidak bisa update data
   - Solusi: Add UPDATE functionality di admin panel
   
3. **No Composite Unique for Registration**: Bisa duplicate (nama + NPSN + cabang)
   - Solusi: Add SQL constraint `UNIQUE (Nama_Peserta, NPSN, ID_Cabang_Lomba)`
   
4. **No Cascade Delete**: Foreign key dengan `ON DELETE RESTRICT`
   - Risk: Cannot delete cabang_lomba if peserta references it
   - Need careful admin workflow

5. **No Soft Delete**: Hard delete only
   - Loss of audit trail ketika delete data
   - Better: Add `deleted_at` column dan logical delete

---

## Performance Index Strategy

```sql
-- Existing indexes (implicit dari PK)
ALTER TABLE peserta_terdaftar 
    ADD INDEX idx_nama (Nama_Peserta),
    ADD INDEX idx_npsn (NPSN),
    ADD INDEX idx_domisili (domisili);

ALTER TABLE domisilikota 
    ADD INDEX idx_nisn (NISN),
    ADD INDEX idx_peserta (Nama_Peserta),
    ADD INDEX idx_cabang (ID_Cabang_Lomba);

-- Same for domisilikabupaten dan domisilikabupaten2

-- Composite index untuk common queries
ALTER TABLE peserta_terdaftar 
    ADD UNIQUE INDEX unique_combo (Nama_Peserta, NPSN, domisili);

ALTER TABLE domisilikota 
    ADD UNIQUE INDEX unique_nisn_cabang (NISN, ID_Cabang_Lomba);
```

---

## Recommendation: Enhanced Schema (Optional)

```sql
-- Tambahan untuk better data integrity
ALTER TABLE domisilikota 
    ADD COLUMN status_verifikasi ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    ADD COLUMN verified_by INT,
    ADD COLUMN verified_at TIMESTAMP NULL,
    ADD COLUMN catatan TEXT,
    ADD FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL;

-- Ini memungkinkan:
-- 1. Admin verify setiap pendaftaran
-- 2. Audit trail siapa yang verify kapan
-- 3. Reject dengan alasan jika ada kesalahan
```

---

**Document Version**: 1.0  
**Last Updated**: 2026-04-09  
**Status**: Analysis Complete
