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:
php artisan make:migration create_products_table
Edit the generated migration file to define the products table schema:
// 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:
php artisan make:migration create_categories_table
Define the categories table schema:
// 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:
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:
php artisan make:migration create_users_table
Define the users table schema:
// 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:
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:
// 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):
// 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:
php artisan make:migration create_orders_table
Define the orders table schema:
// 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:
php artisan migrate
Update the users table model (User.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:
php artisan make:migration create_order_product_table
Define the order_product table schema:
// 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:
php artisan migrate
Update the order and product models (Order.php and Product.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’);
}
}
// 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:
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):
// 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
}
}
// 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:
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!