DB Schema and Functionality Plan

First, what we're creating here?

Imagine a mobile app to park the car somewhere in a big city, to start/stop the parking.

Let's just quickly run through the features that our users would be able to do, those features will later become API endpoints. In a typical simple parking application, I imagine these features:

• User register
• User login
• User view/update profile/password
• Manage user's vehicles
• Get prices for parking zones/areas
• Start/stop parking at a chosen zone
• View the current/total price of parking

Notice: In this application, we will skip the actual payments for parking, because there are so many different ways to pay, depending on the country, so that could be a separate very long course, in the future.

When creating any application, I first start thinking about entities - it is just a more sophisticated word for database tables and Eloquent models. When I have a DB Schema in front of me, visually, then I can list the features of each entity.

So, what are we dealing with, in a parking application?

• Users
• Cars/vehicles
• Parking zones/areas with prices
• Parking events: start/stop

These are the main DB models, from them we can calculate everything and make any report we want.

Visually, the DB schema is this:

Yes, I draw it with pen and paper - even in the 21st century, it's by far the quickest way to let the unstructured thoughts out, visually.

Now, we can get to coding and create this schema with Laravel.

After creating our fresh Laravel project, we create these Models.

php artisan make:model Vehicle -m

The migration of the DB table:

Schema::create('vehicles', function (Blueprint $table) {
    $table->id();
 
    $table->foreignId('user_id')->constrained();
    $table->string('plate_number');
 
    $table->timestamps();
    $table->softDeletes();
});

Also, my personal preference is to immediately specify the $fillable array in every Eloquent model, so I will do exactly that:

app/Models/Vehicle.php:

use Illuminate\Database\Eloquent\SoftDeletes;
 
class Vehicle extends Model
{
    use HasFactory;
    use SoftDeletes;
 
    protected $fillable = ['user_id', 'plate_number'];
}

Next, the parking zone/area entity. Let's just call it "zone" and add a few entries immediately.

php artisan make:model Zone -m

use App\Models\Zone;
 
return new class extends Migration
{
    public function up()
    {
        Schema::create('zones', function (Blueprint $table) {
            $table->id();
 
            $table->string('name');
            $table->integer('price_per_hour');
 
            $table->timestamps();
        });
 
        Zone::create(['name' => 'Green Zone', 'price_per_hour' => 100]);
        Zone::create(['name' => 'Yellow Zone', 'price_per_hour' => 200]);
        Zone::create(['name' => 'Red Zone', 'price_per_hour' => 300]);
    }
};

Did you know that you can use the Eloquent model immediately here in the migrations? Another option is to generate a Seeder class and put the data there, but for the simplicity of this tutorial, I'm doing it right away in the migration.

Also, I agree that the field price_per_hour is not very flexible: every city may have its logic for parking prices - per minute, hour, second, etc. So, in this case, just for clarity, I'm relying on the assumption that it's per hour, and the price will be in cents, as an integer.

And, of course, we need to make those two fields fillable:

app/Models/Zone.php:

class Zone extends Model
{
    use HasFactory;
 
    protected $fillable = ['name', 'price_per_hour'];
}

Finally, when we start/stop parking, we will deal with the DB table "parkings".

php artisan make:model Parking -m

The fields in the table will be these:

Schema::create('parkings', function (Blueprint $table) {
    $table->id();
 
    $table->foreignId('user_id')->constrained();
    $table->foreignId('vehicle_id')->constrained();
    $table->foreignId('zone_id')->constrained();
    $table->dateTime('start_time')->nullable();
    $table->dateTime('stop_time')->nullable();
    $table->integer('total_price')->nullable();
 
    $table->timestamps();
});

As you can see: three foreign key fields, start/stop time, and total_price at the end of the parking event.

And, of course, all fillable:

class Parking extends Model
{
    use HasFactory;
 
    protected $fillable = ['user_id', 'vehicle_id', 'zone_id', 'start_time', 'stop_time', 'total_price'];
 
    protected $casts = [
        'start_time' => 'datetime',
        'stop_time' => 'datetime',
    ];
}

Also, we add the $casts to the datetime columns, to be able to easily convert them later to whichever format we want. You can read more about casts, in this article: Laravel Datetime to Carbon Automatically: $dates or $casts

Later, we will add relationship methods in the models, as we need them in the API endpoints.

But, for now, we're done with the DB structure.

After we run

php artisan migrate

We can generate a more presentable visual schema, with some SQL client tool, like DBeaver in my case: