Laravel — P35: Database Setup (CMP)

Setting the Stage: Database Configuration for the Car Management Project

Time to get our hands dirty and start working on this project. What we want to do is have a Car Management system for our car collection. We’ll add items like the image of our car, the year, make, model, estimated value, etc.

Database Schema

Let’s see what this system schema needs to look like.

To generate the content, we’ll need to create 5 different migrations. To help speed up the process, we know that we’ll need 4 models along with the migrations and 4 factories and seeders most likely. The other one is a pivot table: image_personal_car. We’ll just need a migration for that one. We’ll discuss pivot tables when we start linking all of our relationships up.

# php artisan make:model PersonalCarBrand -mfs

   INFO  Model [app/Models/PersonalCarBrand.php] created successfully.  

   INFO  Factory [database/factories/PersonalCarBrandFactory.php] created successfully.  

   INFO  Migration [database/migrations/2023_01_19_194420_create_personal_car_brands_table.php] created successfully.  

   INFO  Seeder [database/seeders/PersonalCarBrandSeeder.php] created successfully.  
# php artisan make:model PersonalCarModel -mfs

   INFO  Model [app/Models/PersonalCarModel.php] created successfully.  

   INFO  Factory [database/factories/PersonalCarModelFactory.php] created successfully.  

   INFO  Migration [database/migrations/2023_01_19_194503_create_personal_car_models_table.php] created successfully.  

   INFO  Seeder [database/seeders/PersonalCarModelSeeder.php] created successfully.
# php artisan make:model PersonalCar -mfs

   INFO  Model [app/Models/PersonalCar.php] created successfully.  

   INFO  Factory [database/factories/PersonalCarFactory.php] created successfully.  

   INFO  Migration [database/migrations/2023_01_19_194255_create_personal_cars_table.php] created successfully.  

   INFO  Seeder [database/seeders/PersonalCarSeeder.php] created successfully.

The -mfs flag creates a migration, factory, and seeder alongside the model for us.

We won’t need a factory or a seeder for our images. We’re okay with keeping that one blank (and we want to be a little different with that one for the fun of it).

# php artisan make:model Image -m  

   INFO  Model [app/Models/Image.php] created successfully.  

   INFO  Migration [database/migrations/2023_01_19_194540_create_images_table.php] created successfully.

For the pivot table, we just need the migration.

# php artisan make:migration create_image_personal_car_table

   INFO  Migration [2023_01_19_194631_create_image_personal_car_table] created successfully.

The migrations need to be created in that order because we’re going to be using foreign keys. If the table is created and is trying to reference an id in another table, it will fail since that table does not exist yet.

PersonalCarBrand

The personal_car_brands table just needs a couple of fields: name and slug. The slug is so that we can have a url friendly name. Each of these should be unique. We don’t want to repeat Chevrolet twice in there, for example.

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('personal_car_brands', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->string('slug')->unique();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('personal_car_brands');
    }
};

PersonalCarModel

Just like a car can have a brand, it can also have a model, such as the Corvette. The same concept applies to the personal_car_models table like they did for the personal_car_brands table: two fields, name and slug, and those fields should be unique.

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('personal_car_models', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->string('slug')->unique();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('personal_car_models');
    }
};

PersonalCar

The personal_cars table will contain data about our car. We’re not going to store the brand and the model inside of this table. Instead, we’ll store a link to the brands and models tables. The purchase, sales, and current value amounts will be stored as an integer (in pennies). This way we can divide the value by 100 and get our amounts that we need (i.e. 150 is 150/100 or $1.5)

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('personal_cars', function (Blueprint $table) {
            $table->id();
            $table->string('year');
            $table->foreignId('personal_car_brand_id');
            $table->foreignId('personal_car_model_id');
            $table->boolean('is_manual')->default(true);
            $table->string('exterior_color');
            $table->unsignedInteger('purchase_amount');
            $table->unsignedInteger('current_value');
            $table->unsignedInteger('sales_amount');
            $table->date('date_purchased');
            $table->date('date_sold')->nullable()->default(null);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('personal_cars');
    }
};

Image

The images table just needs to store the url of the image and the alt text for it.

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('images', function (Blueprint $table) {
            $table->id();
            $table->string('url');
            $table->string('alt');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('images');
    }
};

We now need to link the images to our personal cars. Each of our cars can have one or more images. So, we need a pivot table between it that contains the car id and the image id foreign keys.

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('image_personal_car', function (Blueprint $table) {
            $table->foreignId('personal_car_id');
            $table->foreignId('image_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('image_personal_car');
    }
};

By convention, pivot tables take the singular version of each table, sorted alphabetically, and then combined with underscores. The tables, images and personal_cars become the image_personal_car pivot table.

All we need to do now is run our migrations.

# php artisan migrate

   INFO  Running migrations.  

  2023_01_19_194202_create_personal_car_brands_table ............... 43ms DONE
  2023_01_19_194203_create_personal_car_models_table ............... 34ms DONE
  2023_01_19_194255_create_personal_cars_table ..................... 15ms DONE
  2023_01_19_194540_create_images_table ............................ 14ms DONE
  2023_01_19_194631_create_image_personal_car_table ................ 14ms DONE

And we’re done. Our database structure is setup. We can verify that we have all of our Models, Factories, and Seeders also already created. We simply need to create some code for them. We’ll do that next time.

Laravel Series

Continue your Laravel Learning.

Laravel — P34: Introduction to the Car Management Project

An Introduction to a Laravel Car Management Project

Laravel – P34: Car Management Project

Dive into the Car Management Project with Laravel. Get a comprehensive guide to building & managing a vehicle database efficiently.

Laravel — P35: Database Setup (CMP)

Setting the Stage: Database Configuration for the Car Management Project

Laravel — P35: Database Setup (CMP)

Prepare your CMP with Laravel: A step-by-step guide to setting up your database. Ensure a solid foundation for your vehicle management app.

Laravel — P35: Database Setup (CMP)

XXXXXXXX

Laravel – P36: xxx

XXXXXXX

Leave a Reply