Course & Lessons

A learning management system structure with courses containing ordered lessons and tracking user enrollment and progress.

ERD Concept

One-to-Many + Many-to-Many: Courses have many lessons. Users enroll in courses. Lessons track completion per user.

Tables

courses

id instructor_id (FK) title slug description price is_published timestamps

lessons

id course_id (FK) title slug content video_url sort_order duration_minutes timestamps

enrollments

id user_id (FK) course_id (FK) enrolled_at completed_at timestamps

lesson_progress

id user_id (FK) lesson_id (FK) completed completed_at timestamps

Relationships

Course belongsTo User (instructor)
Course hasMany Lesson
Course hasMany Enrollment
Lesson belongsTo Course
Enrollment belongsTo User
Enrollment belongsTo Course
LessonProgress belongsTo User
LessonProgress belongsTo Lesson

Migration

Schema::create('courses', function (Blueprint $table) {
    $table->id();
    $table->foreignId('instructor_id')->constrained('users')->cascadeOnDelete();
    $table->string('title');
    $table->string('slug')->unique();
    $table->text('description')->nullable();
    $table->decimal('price', 8, 2)->default(0);
    $table->boolean('is_published')->default(false);
    $table->timestamps();
});

Schema::create('lessons', function (Blueprint $table) {
    $table->id();
    $table->foreignId('course_id')->constrained()->cascadeOnDelete();
    $table->string('title');
    $table->string('slug');
    $table->longText('content')->nullable();
    $table->string('video_url')->nullable();
    $table->unsignedInteger('sort_order')->default(0);
    $table->unsignedInteger('duration_minutes')->default(0);
    $table->timestamps();
    $table->index(['course_id', 'sort_order']);
});

Schema::create('enrollments', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->foreignId('course_id')->constrained()->cascadeOnDelete();
    $table->timestamp('enrolled_at');
    $table->timestamp('completed_at')->nullable();
    $table->timestamps();
    $table->unique(['user_id', 'course_id']);
});

Schema::create('lesson_progress', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->foreignId('lesson_id')->constrained()->cascadeOnDelete();
    $table->boolean('completed')->default(false);
    $table->timestamp('completed_at')->nullable();
    $table->timestamps();
    $table->unique(['user_id', 'lesson_id']);
});