P4 | Creating the Database Structure for a Laravel Training App

Hello everyone, I’m Samuel from the Craftable PRO team. Now that we’ve installed Laravel and Craftable PRO and reviewed the core features, it’s time to start building the backend for our training app! In this article, we’ll lay out the database structure, setting up the foundational tables and relationships necessary for managing multilingual training content, exercises, and user participation.

Defining the Database Structure

For our training app, we’ll have tables to manage Trainings and Exercises, both of which will support multiple languages. Users can join individual training sessions, and we’ll leverage Craftable PRO’s built-in Localization feature to handle translations.

Key Tables and Relationships

Our database structure includes:

• Trainings: Stores the title and description of each training session.
• Exercises: Each training will include multiple exercises, with details like title, description, and duration.
• Users: Laravel’s default users table is sufficient here.
• Pivot Table (training_user): Tracks which users have joined which trainings.

Database Structure Overview

Our table setup will look like this:

Trainings Table

• id: Primary key
• title (jsonb): Multilingual titles
• text (jsonb): Multilingual descriptions
• Timestamps (created and updated)

Exercises Table

• id: Primary key
• title (jsonb): Multilingual titles
• text (jsonb): Multilingual descriptions
• duration (integer): Exercise duration in seconds
• training_id (foreign key): References trainings table
• Timestamps

Training_user Table (Pivot Table)

• training_id: Foreign key to trainings table
• user_id: Foreign key to users table

Why Use JSONB for Translatable Fields?

Craftable PRO’s Localization feature makes it easy to manage multilingual data by storing translatable columns in a JSON structure. This allows us to edit each language directly within the admin panel, and the app will automatically load the appropriate language version on the frontend based on user settings.

Creating Migrations for the Tables

Let’s move on to creating the database tables with Laravel’s Artisan command.

Open your terminal and enter the following commands to generate migrations:

php artisan make:migration create_trainings_table 
php artisan make:migration create_exercises_table 
php artisan make:migration create_training_user_table

Now, let’s add the necessary fields to each table’s migration file.

Trainings Table Migration

In the create_trainings_table migration, define the structure as follows:

Schema::create('trainings', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
    $table->jsonb('title');
    $table->jsonb('text');
});

Exercises Table Migration

In the create_exercises_table migration, set up the columns as shown below:

Schema::create('exercises', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
    $table->jsonb('title');
    $table->jsonb('text');
    $table->integer('duration');
    $table->foreignId('training_id')->constrained()->onDelete('cascade');
});

Training_User Pivot Table Migration

Lastly, for the pivot table that links users and trainings, add the following code in the create_training_user_table migration:

Schema::create('training_user', function (Blueprint $table) {
    $table->foreignId('training_id')->constrained()->onDelete('cascade');
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
});

Adding Images for Trainings and Exercises

Both trainings and exercises will support image uploads, a feature Craftable PRO provides out-of-the-box. In the next video, we’ll dive into admin generation and demonstrate how to manage images seamlessly within the Craftable PRO admin panel.

With our database structure in place, we’re ready to bring our training app to life. In the next video, we’ll start building the admin interface for managing these tables. Stay tuned!