BAB 26. DATABASE DESIGN

Prinsip Desain

Database TemuBelajar dirancang dengan prinsip:

Prinsip	Penjelasan
Tenant-ready	Setiap tabel memiliki school_id untuk isolasi data per sekolah
3NF	Data dinormalisasi hingga Third Normal Form
Laravel Eloquent Friendly	Konvensi penamaan sesuai standar Laravel
Scalable	Mendukung horizontal scaling dan caching

---

Daftar Tabel Inti

Tabel	Primary Key	Fungsi
schools	id	Master data sekolah (tenant)
academic_years	id	Tahun ajaran aktif per sekolah
classrooms	id	Rombel/kelas per sekolah
users	id	Autentikasi semua pengguna
students	id	Data akademik siswa
teachers	id	Data guru
subjects	id	Master mata pelajaran
topics	id	Master materi per mata pelajaran
skills	id	Master kompetensi
student_skills	id	Pivot: skill yang dikuasai siswa
requests	id	Permintaan bantuan belajar
bookings	id	Perjanjian siswa dan tutor
meetings	id	Sesi pertemuan tatap muka
meeting_participants	id	Peserta dalam satu sesi
ratings	id	Penilaian setelah sesi
social_impact_scores	id	Akumulasi SIS per periode
achievements	id	Badge dan pencapaian
notifications	id	Riwayat notifikasi

---

Struktur Tabel Detail

users

Kolom	Tipe	Nullable	Keterangan
id	bigint UNSIGNED	No	Primary Key
school_id	bigint UNSIGNED	No	FK → schools
name	varchar(255)	No	Nama lengkap
username	varchar(50)	No	NISN atau NIP, unik
password	varchar(255)	No	Hash bcrypt/Argon2
role	enum	No	student, teacher, admin, super_admin
must_change_password	tinyint(1)	No	Default: 1 (true)
is_active	tinyint(1)	No	Default: 1 (true)
last_login_at	timestamp	Yes	Waktu login terakhir
created_at	timestamp	No	—
updated_at	timestamp	No	—

students

Kolom	Tipe	Nullable	Keterangan
id	bigint UNSIGNED	No	Primary Key
user_id	bigint UNSIGNED	No	FK → users (unique)
school_id	bigint UNSIGNED	No	FK → schools
classroom_id	bigint UNSIGNED	Yes	FK → classrooms
nisn	varchar(10)	No	Unik
level	tinyint	No	Default: 1
xp	int	No	Default: 0
sis_score	decimal(8,2)	No	Default: 0.00
total_sessions_as_tutor	int	No	Default: 0
total_sessions_as_learner	int	No	Default: 0

requests

Kolom	Tipe	Nullable	Keterangan
id	bigint UNSIGNED	No	Primary Key
school_id	bigint UNSIGNED	No	FK → schools
student_id	bigint UNSIGNED	No	FK → students (host)
subject_id	bigint UNSIGNED	No	FK → subjects
topic_id	bigint UNSIGNED	Yes	FK → topics
description	text	Yes	Deskripsi masalah
preferred_location	varchar(255)	No	Nama lokasi
latitude	decimal(10,8)	Yes	Koordinat GPS
longitude	decimal(11,8)	Yes	Koordinat GPS
preferred_time	datetime	No	Waktu yang diinginkan
duration_minutes	smallint	No	Default: 60
tutor_count	tinyint	No	Default: 1
status	enum	No	pending, searching, accepted, rejected, on_going, completed, cancelled
expired_at	timestamp	Yes	Batas waktu request aktif

bookings

Kolom	Tipe	Nullable	Keterangan
id	bigint UNSIGNED	No	Primary Key
request_id	bigint UNSIGNED	No	FK → requests
tutor_id	bigint UNSIGNED	No	FK → students
status	enum	No	pending, accepted, rejected, rescheduled, cancelled
scheduled_at	datetime	Yes	Jadwal disepakati
matching_score	decimal(5,2)	Yes	Skor AI Matching
rejection_reason	text	Yes	Alasan penolakan
reschedule_time	datetime	Yes	Waktu alternatif

meetings

Kolom	Tipe	Nullable	Keterangan
id	bigint UNSIGNED	No	Primary Key
booking_id	bigint UNSIGNED	No	FK → bookings (unique)
qr_token	varchar(100)	No	Token QR Check-In
qr_expires_at	timestamp	No	Kadaluarsa QR
check_in_at	timestamp	Yes	Waktu Check-In
check_out_at	timestamp	Yes	Waktu Check-Out
duration_actual	smallint	Yes	Durasi nyata (menit)
summary	text	Yes	Ringkasan diskusi
completion_status	enum	Yes	solved, partially_solved, not_solved

ratings

Kolom	Tipe	Nullable	Keterangan
id	bigint UNSIGNED	No	Primary Key
meeting_id	bigint UNSIGNED	No	FK → meetings
rater_id	bigint UNSIGNED	No	FK → users (pemberi rating)
ratee_id	bigint UNSIGNED	No	FK → users (penerima rating)
score	tinyint	No	1–5
comment	text	Yes	Komentar opsional
is_solved	tinyint(1)	Yes	Apakah masalah selesai