Laravel e commerce tutorial Part 3: Creating the E-Commerce Database Schema

Wed-Nov-2023
34

Part 3: Creating the E-Commerce Database Schema

Now that your Laravel development environment is set up, it’s time to design and create the database schema for your e-commerce application. A well-structured database is crucial for managing products, orders, and user data efficiently.

3.1 Designing the Database Tables

In this section, we’ll focus on creating the initial database tables for your e-commerce application. We’ll start with essential tables for products, categories, and users.

Step 1: Products Table

Create a migration for the products table:

bash
php artisan make:migration create_products_table

Edit the generated migration file to define the products table schema:

php

// database/migrations/YYYY_MM_DD_create_products_table.php

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

class CreateProductsTable extends Migration
{
public function up()
{
Schema::create(‘products’, function (Blueprint $table) {
$table->id();
$table->string(‘name’);
$table->text(‘description’);
$table->decimal(‘price’, 8, 2);
$table->integer(‘stock_quantity’);
$table->timestamps();
});
}

public function down()
{
Schema::dropIfExists(‘products’);
}
}

Run the migration to create the products table:

bash

php artisan migrate

Step 2: Categories Table

Create a migration for the categories table:

bash
php artisan make:migration create_categories_table

Define the categories table schema:

php

// database/migrations/YYYY_MM_DD_create_categories_table.php

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

class CreateCategoriesTable extends Migration
{
public function up()
{
Schema::create(‘categories’, function (Blueprint $table) {
$table->id();
$table->string(‘name’);
$table->timestamps();
});
}

public function down()
{
Schema::dropIfExists(‘categories’);
}
}

Run the migration for the categories table:

bash
php artisan migrate

Step 3: Users Table (if not using Jetstream)

If you’re not using Laravel Jetstream, create a migration for the users table:

bash
php artisan make:migration create_users_table

Define the users table schema:

php

// database/migrations/YYYY_MM_DD_create_users_table.php

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

class CreateUsersTable extends Migration
{
public function up()
{
Schema::create(‘users’, function (Blueprint $table) {
$table->id();
$table->string(‘name’);
$table->string(’email’)->unique();
$table->timestamp(’email_verified_at’)->nullable();
$table->string(‘password’);
$table->rememberToken();
$table->timestamps();
});
}

public function down()
{
Schema::dropIfExists(‘users’);
}
}

Run the migration for the users table:

bash
php artisan migrate

3.2 Setting Up Relationships

Now that the basic tables are created, let’s establish relationships between them.

Step 1: Products and Categories Relationship

Modify the products table migration to include a category_id foreign key:

php

// In the create_products_table migration

public function up()
{
Schema::create(‘products’, function (Blueprint $table) {
$table->id();
$table->string(‘name’);
$table->text(‘description’);
$table->decimal(‘price’, 8, 2);
$table->integer(‘stock_quantity’);
$table->unsignedBigInteger(‘category_id’);
$table->timestamps();

$table->foreign(‘category_id’)->references(‘id’)->on(‘categories’)->onDelete(‘cascade’);
});
}

Update the products table model (Product.php):

php

// app/Models/Product.php

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
protected $fillable = [‘name’, ‘description’, ‘price’, ‘stock_quantity’, ‘category_id’];

public function category()
{
return $this->belongsTo(Category::class);
}
}

Step 2: Users and Orders Relationship

If you’re not using Laravel Jetstream, create an orders table migration:

bash
php artisan make:migration create_orders_table

Define the orders table schema:

php

// database/migrations/YYYY_MM_DD_create_orders_table.php

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

class CreateOrdersTable extends Migration
{
public function up()
{
Schema::create(‘orders’, function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger(‘user_id’);
$table->timestamps();

$table->foreign(‘user_id’)->references(‘id’)->on(‘users’)->onDelete(‘cascade’);
});
}

public function down()
{
Schema::dropIfExists(‘orders’);
}
}

Run the migration for the orders table:

bash
php artisan migrate

Update the users table model (User.php):

php

// app/Models/User.php

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
// Existing code…

public function orders()
{
return $this->hasMany(Order::class);
}
}

Step 3: Orders and Products Relationship

Create a migration for the order_product pivot table:

bash
php artisan make:migration create_order_product_table

Define the order_product table schema:

php

// database/migrations/YYYY_MM_DD_create_order_product_table.php

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

class CreateOrderProductTable extends Migration
{
public function up()
{
Schema::create(‘order_product’, function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger(‘order_id’);
$table->unsignedBigInteger(‘product_id’);
$table->integer(‘quantity’);
$table->timestamps();

$table->foreign(‘order_id’)->references(‘id’)->on(‘orders’)->onDelete(‘cascade’);
$table->foreign(‘product_id’)->references(‘id’)->on(‘products’)->onDelete(‘cascade’);
});
}

public function down()
{
Schema::dropIfExists(‘order_product’);
}
}

Run the migration for the order_product table:

bash
php artisan migrate

Update the order and product models (Order.php and Product.php):

php

// app/Models/Order.php

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
public function user()
{
return $this->belongsTo(User::class);
}

public function products()
{
return $this->belongsToMany(Product::class)->withPivot(‘quantity’);
}
}

php

// app/Models/Product.php

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
// Existing code…

public function orders()
{
return $this->belongsToMany(Order::class)->withPivot(‘quantity’);
}
}

Congratulations! You’ve successfully designed

the initial database schema and established relationships between key tables for your e-commerce application. With these foundations in place, your application can effectively manage products, categories, users, and orders.

3.3 Seeding the Database with Sample Data

Now that your database structure is set up, let’s populate it with some sample data for testing and development purposes.

Step 1: Create Seeders

Create seeders for the products and categories:

bash
php artisan make:seeder CategorySeeder
php artisan make:seeder ProductSeeder

Step 2: Define Seed Data

Define sample data in the respective seeder files (CategorySeeder.php and ProductSeeder.php):

php

// database/seeders/CategorySeeder.php

use Illuminate\Database\Seeder;
use App\Models\Category;

class CategorySeeder extends Seeder
{
public function run()
{
Category::create([‘name’ => ‘Electronics’]);
Category::create([‘name’ => ‘Clothing’]);
// Add more categories as needed
}
}

php

// database/seeders/ProductSeeder.php

use Illuminate\Database\Seeder;
use App\Models\Product;

class ProductSeeder extends Seeder
{
public function run()
{
// Assuming you have at least two categories
$categoryElectronics = App\Models\Category::where(‘name’, ‘Electronics’)->first();
$categoryClothing = App\Models\Category::where(‘name’, ‘Clothing’)->first();

Product::create([
‘name’ => ‘Smartphone’,
‘description’ => ‘The latest smartphone with advanced features.’,
‘price’ => 599.99,
‘stock_quantity’ => 50,
‘category_id’ => $categoryElectronics->id,
]);

Product::create([
‘name’ => ‘Laptop’,
‘description’ => ‘Powerful laptop for work and entertainment.’,
‘price’ => 1299.99,
‘stock_quantity’ => 30,
‘category_id’ => $categoryElectronics->id,
]);

Product::create([
‘name’ => ‘T-Shirt’,
‘description’ => ‘Comfortable cotton T-shirt.’,
‘price’ => 19.99,
‘stock_quantity’ => 100,
‘category_id’ => $categoryClothing->id,
]);

// Add more products as needed
}
}

Step 3: Run Seeders

Run the seeders to populate the database:

bash
php artisan db:seed --class=CategorySeeder
php artisan db:seed --class=ProductSeeder

Your database is now populated with sample data, allowing you to test your application’s functionality.

In the next part of the tutorial, we’ll focus on building the user authentication system in Laravel. Stay tuned for the next steps in creating your e-commerce application!