Laravel E-Learning Part 3: Database Schema Design

Laravel E-Learning Rebuild

Part 1: Introduction & Why I'm Upgrading

Part 2: Setting Up Laravel 11 + Filament

Part 3: Database Schema Design

Part 4: Authentication & User Roles

Part 5: Building the Exam System

Part 6: Real-Time Features

Part 7: Testing Strategy

Part 8: Deployment

5 Januari 2026

Laravel E-Learning Part 3: Database Schema Design

Designing a robust database schema for an e-learning platform with users, subjects, exams, questions, and results using Laravel migrations.

4 min read

Entity Relationship Overview

Before writing any code, let's understand our data model:

Entity Relationship Diagram

E-Learning Database ERD

Tables Overview

Table	Description	Key Relationships
users	All users (Admin, Teacher, Student)	Has roles via Spatie
subjects	Course subjects (Math, Physics, etc.)	Belongs to Teacher
exams	Individual exams/tests	Belongs to Subject, has many Questions
questions	Exam questions	Belongs to Exam
exam_attempts	Student's exam session	Belongs to Exam and Student
answers	Student's answers	Belongs to ExamAttempt and Question

Laravel Eloquent Relationships

Model	Relationship	Related Model
User (Teacher)	`hasMany`	Subject
Subject	`belongsTo`	User
Subject	`hasMany`	Exam
Exam	`belongsTo`	Subject
Exam	`hasMany`	Question
Exam	`hasMany`	ExamAttempt
Question	`belongsTo`	Exam
ExamAttempt	`belongsTo`	User (Student)
ExamAttempt	`belongsTo`	Exam
ExamAttempt	`hasMany`	Answer
Answer	`belongsTo`	ExamAttempt
Answer	`belongsTo`	Question

Step 1: Create Migrations

Subjects Table

php artisan make:migration create_subjects_table

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('subjects', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('code')->unique();
            $table->text('description')->nullable();
            $table->foreignId('teacher_id')->constrained('users')->onDelete('cascade');
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('subjects');
    }
};

Exams Table

php artisan make:migration create_exams_table

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('exams', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->text('description')->nullable();
            $table->foreignId('subject_id')->constrained()->onDelete('cascade');
            $table->integer('duration_minutes')->default(60);
            $table->integer('passing_score')->default(60);
            $table->integer('total_questions')->default(0);
            $table->dateTime('start_time');
            $table->dateTime('end_time');
            $table->enum('status', ['draft', 'published', 'closed'])->default('draft');
            $table->boolean('shuffle_questions')->default(false);
            $table->boolean('show_result')->default(true);
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('exams');
    }
};

Questions Table

php artisan make:migration create_questions_table

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('questions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('exam_id')->constrained()->onDelete('cascade');
            $table->text('question_text');
            $table->enum('question_type', ['multiple_choice', 'true_false', 'essay'])->default('multiple_choice');
            $table->json('options')->nullable(); // For multiple choice
            $table->string('correct_answer');
            $table->integer('points')->default(1);
            $table->integer('order')->default(0);
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('questions');
    }
};

Exam Attempts Table

php artisan make:migration create_exam_attempts_table

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('exam_attempts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('exam_id')->constrained()->onDelete('cascade');
            $table->foreignId('student_id')->constrained('users')->onDelete('cascade');
            $table->dateTime('started_at');
            $table->dateTime('completed_at')->nullable();
            $table->integer('score')->nullable();
            $table->integer('correct_answers')->default(0);
            $table->integer('total_answered')->default(0);
            $table->enum('status', ['in_progress', 'completed', 'timed_out'])->default('in_progress');
            $table->timestamps();
            
            $table->unique(['exam_id', 'student_id']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('exam_attempts');
    }
};

Answers Table

php artisan make:migration create_answers_table

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('answers', function (Blueprint $table) {
            $table->id();
            $table->foreignId('exam_attempt_id')->constrained()->onDelete('cascade');
            $table->foreignId('question_id')->constrained()->onDelete('cascade');
            $table->text('answer')->nullable();
            $table->boolean('is_correct')->nullable();
            $table->integer('points_earned')->default(0);
            $table->timestamps();
            
            $table->unique(['exam_attempt_id', 'question_id']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('answers');
    }
};

Step 2: Create Models

Subject Model

php artisan make:model Subject

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Subject extends Model
{
    use HasFactory;

    protected $fillable = [
        'name',
        'code',
        'description',
        'teacher_id',
        'is_active',
    ];

    protected $casts = [
        'is_active' => 'boolean',
    ];

    public function teacher(): BelongsTo
    {
        return $this->belongsTo(User::class, 'teacher_id');
    }

    public function exams(): HasMany
    {
        return $this->hasMany(Exam::class);
    }
}

Exam Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Exam extends Model
{
    use HasFactory;

    protected $fillable = [
        'title',
        'description',
        'subject_id',
        'duration_minutes',
        'passing_score',
        'total_questions',
        'start_time',
        'end_time',
        'status',
        'shuffle_questions',
        'show_result',
    ];

    protected $casts = [
        'start_time' => 'datetime',
        'end_time' => 'datetime',
        'shuffle_questions' => 'boolean',
        'show_result' => 'boolean',
    ];

    public function subject(): BelongsTo
    {
        return $this->belongsTo(Subject::class);
    }

    public function questions(): HasMany
    {
        return $this->hasMany(Question::class);
    }

    public function attempts(): HasMany
    {
        return $this->hasMany(ExamAttempt::class);
    }

    public function isActive(): bool
    {
        $now = now();
        return $this->status === 'published' 
            && $now->between($this->start_time, $this->end_time);
    }
}

Question Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Question extends Model
{
    use HasFactory;

    protected $fillable = [
        'exam_id',
        'question_text',
        'question_type',
        'options',
        'correct_answer',
        'points',
        'order',
    ];

    protected $casts = [
        'options' => 'array',
    ];

    public function exam(): BelongsTo
    {
        return $this->belongsTo(Exam::class);
    }
}

Step 3: Run Migrations

php artisan migrate

Step 4: Create Seeders

php artisan make:seeder SubjectSeeder

<?php

namespace Database\Seeders;

use App\Models\Subject;
use App\Models\User;
use Illuminate\Database\Seeder;

class SubjectSeeder extends Seeder
{
    public function run(): void
    {
        $teacher = User::first();

        $subjects = [
            ['name' => 'Mathematics', 'code' => 'MATH101'],
            ['name' => 'Physics', 'code' => 'PHYS101'],
            ['name' => 'English', 'code' => 'ENG101'],
            ['name' => 'Computer Science', 'code' => 'CS101'],
        ];

        foreach ($subjects as $subject) {
            Subject::create([
                ...$subject,
                'teacher_id' => $teacher->id,
            ]);
        }
    }
}

Summary

We've designed a complete database schema with:

✅ Subjects managed by teachers
✅ Exams with configurable settings
✅ Questions with multiple types
✅ Student exam attempts and answers
✅ Proper relationships and constraints

What's Next

In Part 4, we'll implement authentication and user roles:

Role-based access (Admin, Teacher, Student)
Separate Filament panels for different roles
Permission management with Spatie

Continue to Part 4: Authentication & User Roles →