| name | database-design |
| description | Design database schemas with Laravel migrations, Eloquent relationships, and indexing strategies. Use when creating migrations, designing table schemas, defining relationships between models, optimizing queries with indexes, or planning database architecture. Triggers on migration, database schema, table design, foreign key, index, relationship, Eloquent, or query optimization.
|
Database Design with Laravel
Migration Best Practices
Naming Conventions
Laravel uses snake_case naming for migration files. The artisan generator infers the
table name from the migration name:
php artisan make:migration create_orders_table
php artisan make:migration create_order_items_table
php artisan make:migration add_status_to_orders_table
php artisan make:migration add_tracking_number_to_orders_table
php artisan make:migration rename_title_to_name_on_products_table
php artisan make:migration drop_legacy_column_from_users_table
One Change Per Migration
Each migration should do one logical thing. This makes rollbacks predictable:
public function up(): void
{
Schema::create('orders', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id')->constrained()->cascadeOnDelete();
$table->foreignId('customer_id')->constrained();
$table->string('order_number')->unique();
$table->enum('status', ['pending', 'confirmed', 'shipped', 'delivered', 'cancelled'])
->default('pending');
$table->decimal('subtotal', 10, 2);
$table->decimal('tax', 10, 2)->default(0);
$table->decimal('total', 10, 2);
$table->text('notes')->nullable();
$table->timestamp('shipped_at')->nullable();
$table->timestamp('delivered_at')->nullable();
$table->timestamps();
$table->softDeletes();
});
}
public function down(): void
{
Schema::dropIfExists('orders');
}
Always Implement down()
The down() method must reverse exactly what up() does. This allows safe rollbacks:
public function up(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->string('status')->default('pending')->after('total');
});
}
public function down(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->dropColumn('status');
});
}
Standard Column Helpers
Always use the built-in helpers for consistency:
$table->id();
$table->uuid('id')->primary();
$table->ulid('id')->primary();
$table->timestamps();
$table->softDeletes();
$table->rememberToken();
Column Types Reference
| Migration Method | MySQL Type | PostgreSQL Type | Purpose |
|---|
$table->id() | BIGINT UNSIGNED AI PK | BIGSERIAL PK | Auto-incrementing primary key |
$table->uuid('id') | CHAR(36) | UUID | UUID column |
$table->ulid('id') | CHAR(26) | CHAR(26) | ULID column |
$table->string('name') | VARCHAR(255) | VARCHAR(255) | Short text (names, emails) |
$table->string('code', 10) | VARCHAR(10) | VARCHAR(10) | Short text with max length |
$table->text('body') | TEXT | TEXT | Long text (descriptions) |
$table->mediumText('content') | MEDIUMTEXT | TEXT | Medium-length content |
$table->longText('payload') | LONGTEXT | TEXT | Very long content (JSON blobs) |
$table->integer('qty') | INT | INTEGER | Standard integer |
$table->bigInteger('views') | BIGINT | BIGINT | Large integer |
$table->unsignedInteger('qty') | INT UNSIGNED | INTEGER | Non-negative integer |
$table->tinyInteger('level') | TINYINT | SMALLINT | Small integer (0-127) |
$table->boolean('active') | TINYINT(1) | BOOLEAN | True/false flag |
$table->decimal('price', 10, 2) | DECIMAL(10,2) | NUMERIC(10,2) | Exact decimal (money) |
$table->float('rating') | FLOAT | REAL | Approximate decimal |
$table->date('birth_date') | DATE | DATE | Date without time |
$table->dateTime('starts_at') | DATETIME | TIMESTAMP | Date with time |
$table->timestamp('sent_at') | TIMESTAMP | TIMESTAMP | Timestamp |
$table->time('opens_at') | TIME | TIME | Time without date |
$table->year('vintage') | YEAR | INTEGER | Year only |
$table->json('metadata') | JSON | JSONB | JSON data |
$table->enum('status', [...]) | ENUM | VARCHAR + CHECK | Enumerated values |
$table->binary('photo') | BLOB | BYTEA | Binary data |
$table->ipAddress('visitor') | VARCHAR(45) | INET | IPv4 or IPv6 address |
$table->macAddress('device') | VARCHAR(17) | MACADDR | MAC address |
Column Modifiers
$table->string('email')->unique();
$table->string('nickname')->nullable();
$table->string('status')->default('active');
$table->integer('position')->unsigned();
$table->text('bio')->nullable()->after('email');
$table->string('slug')->comment('URL slug');
$table->string('legacy')->virtualAs("CONCAT(first_name, ' ', last_name)");
Index Strategies
When to Add Indexes
Add indexes on columns that appear in:
WHERE clauses (equality and range lookups)
JOIN conditions (foreign keys)
ORDER BY clauses (sorting)
GROUP BY clauses (aggregation)
Do NOT index columns that:
- Have very low cardinality (e.g., a boolean with 50/50 distribution on a small table)
- Are rarely queried
- Are on tables with very few rows
Creating Indexes
Schema::create('orders', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id')->constrained();
$table->string('status');
$table->timestamp('created_at');
$table->index('status');
$table->index(['user_id', 'status', 'created_at']);
$table->unique('order_number');
$table->unique(['user_id', 'product_id']);
});
Composite Index Column Order
The leftmost prefix rule means a composite index (user_id, status, created_at) can
satisfy queries that filter on:
user_id alone
user_id AND status
user_id AND status AND created_at
It will NOT help queries that filter on:
status alone (needs its own index)
created_at alone
Put the most selective column first, and the range column last.
Full-Text Indexes
$table->fullText('body');
$table->fullText(['title', 'body']);
Product::whereFullText('description', 'wireless headphones')->get();
Product::whereFullText(['title', 'description'], 'wireless headphones')->get();
Adding Indexes to Existing Tables
public function up(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->index('status', 'orders_status_index');
$table->index(['user_id', 'created_at'], 'orders_user_created_index');
});
}
public function down(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->dropIndex('orders_status_index');
$table->dropIndex('orders_user_created_index');
});
}
Foreign Key Conventions
Standard Foreign Keys
$table->foreignId('user_id')->constrained()->cascadeOnDelete();
$table->foreignId('author_id')->constrained('users')->cascadeOnDelete();
$table->foreignId('category_id')->nullable()->constrained()->nullOnDelete();
On Delete Behaviors
| Method | SQL | Behavior |
|---|
cascadeOnDelete() | ON DELETE CASCADE | Delete child rows when parent is deleted |
nullOnDelete() | ON DELETE SET NULL | Set foreign key to NULL (column must be nullable) |
restrictOnDelete() | ON DELETE RESTRICT | Prevent parent deletion if children exist |
noActionOnDelete() | ON DELETE NO ACTION | Same as RESTRICT in most databases |
On Update Behaviors
$table->foreignId('user_id')->constrained()->cascadeOnUpdate()->cascadeOnDelete();
Choosing the Right Delete Behavior
- cascadeOnDelete: Order -> OrderItems (items are meaningless without the order)
- nullOnDelete: Post -> author_id (keep the post even if the user is deleted)
- restrictOnDelete: User -> Orders (prevent deleting users who have orders)
Eloquent Relationships
1. One-to-One: hasOne / belongsTo
$table->id();
$table->foreignId('user_id')->unique()->constrained()->cascadeOnDelete();
$table->string('bio')->nullable();
$table->string('avatar_url')->nullable();
$table->timestamps();
public function profile(): HasOne
{
return $this->hasOne(Profile::class);
}
public function user(): BelongsTo
{
return $this->belongsTo(User::class);
}
$user->profile;
$user->profile()->create(['bio' => 'Developer']);
$profile->user;
2. One-to-Many: hasMany / belongsTo
$table->id();
$table->foreignId('order_id')->constrained()->cascadeOnDelete();
$table->foreignId('product_id')->constrained();
$table->integer('quantity');
$table->decimal('unit_price', 10, 2);
$table->timestamps();
public function items(): HasMany
{
return $this->hasMany(OrderItem::class);
}
public function order(): BelongsTo
{
return $this->belongsTo(Order::class);
}
$order->items;
$order->items()->where('quantity', '>', 1)->get();
$order->items()->create([...]);
3. Many-to-Many: belongsToMany
$table->id();
$table->foreignId('product_id')->constrained()->cascadeOnDelete();
$table->foreignId('tag_id')->constrained()->cascadeOnDelete();
$table->timestamps();
$table->unique(['product_id', 'tag_id']);
public function tags(): BelongsToMany
{
return $this->belongsToMany(Tag::class)->withTimestamps();
}
public function products(): BelongsToMany
{
return $this->belongsToMany(Product::class)->withTimestamps();
}
$product->tags()->attach([1, 2, 3]);
$product->tags()->detach([2]);
$product->tags()->sync([1, 3, 5]);
$product->tags()->syncWithoutDetaching([4]);
4. Has-Through: hasOneThrough / hasManyThrough
public function posts(): HasManyThrough
{
return $this->hasManyThrough(Post::class, User::class);
}
$country->posts;
5. Polymorphic: morphOne / morphMany / morphTo
$table->id();
$table->morphs('commentable');
$table->foreignId('user_id')->constrained();
$table->text('body');
$table->timestamps();
public function comments(): MorphMany
{
return $this->morphMany(Comment::class, 'commentable');
}
public function comments(): MorphMany
{
return $this->morphMany(Comment::class, 'commentable');
}
public function commentable(): MorphTo
{
return $this->morphTo();
}
Relation::enforceMorphMap([
'post' => Post::class,
'video' => Video::class,
]);
6. Many-to-Many Polymorphic: morphToMany / morphedByMany
$table->id();
$table->foreignId('tag_id')->constrained()->cascadeOnDelete();
$table->morphs('taggable');
$table->unique(['tag_id', 'taggable_type', 'taggable_id']);
public function tags(): MorphToMany
{
return $this->morphToMany(Tag::class, 'taggable');
}
public function tags(): MorphToMany
{
return $this->morphToMany(Tag::class, 'taggable');
}
public function posts(): MorphedByMany
{
return $this->morphedByMany(Post::class, 'taggable');
}
public function videos(): MorphedByMany
{
return $this->morphedByMany(Video::class, 'taggable');
}
Pivot Tables
Extra Columns on Pivot
Schema::create('order_product', function (Blueprint $table) {
$table->id();
$table->foreignId('order_id')->constrained()->cascadeOnDelete();
$table->foreignId('product_id')->constrained();
$table->integer('quantity')->default(1);
$table->decimal('unit_price', 10, 2);
$table->timestamps();
$table->unique(['order_id', 'product_id']);
});
public function products(): BelongsToMany
{
return $this->belongsToMany(Product::class)
->withPivot('quantity', 'unit_price')
->withTimestamps();
}
foreach ($order->products as $product) {
echo $product->pivot->quantity;
echo $product->pivot->unit_price;
}
$order->products()->attach($productId, [
'quantity' => 2,
'unit_price' => 29.99,
]);
Custom Pivot Model
use Illuminate\Database\Eloquent\Relations\Pivot;
class OrderProduct extends Pivot
{
protected $casts = [
'unit_price' => 'decimal:2',
];
public function getLineTotalAttribute(): float
{
return $this->quantity * $this->unit_price;
}
}
public function products(): BelongsToMany
{
return $this->belongsToMany(Product::class)
->using(OrderProduct::class)
->withPivot('quantity', 'unit_price')
->withTimestamps();
}
Soft Deletes
Setup
$table->softDeletes();
use Illuminate\Database\Eloquent\SoftDeletes;
class Order extends Model
{
use SoftDeletes;
}
Query Behavior
Order::all();
Order::withTrashed()->get();
Order::onlyTrashed()->get();
$order->trashed();
$order->restore();
$order->forceDelete();
Unique Constraint with Soft Deletes
A unique constraint on email will conflict with soft-deleted records. Solutions:
DB::statement('CREATE UNIQUE INDEX users_email_unique ON users (email) WHERE deleted_at IS NULL');
$table->unique(['email', 'deleted_at']);
public static function booted(): void
{
static::softDeleted(function (User $user) {
$user->updateQuietly(['email' => $user->email . '::deleted::' . $user->id]);
});
}
When to Use Soft Deletes
Use soft deletes when:
- Records must be auditable (orders, invoices, financial data)
- Records might need to be restored
- Related data references the record and you need referential integrity
Avoid soft deletes when:
- Data is truly disposable (logs, temporary records)
- Table will grow very large and soft-deleted records add overhead
- You are using the table as a queue or buffer
Database Transactions
Automatic Transaction with Retry
use Illuminate\Support\Facades\DB;
$order = DB::transaction(function () use ($request) {
$order = Order::create([
'user_id' => auth()->id(),
'total' => 0,
]);
foreach ($request->items as $item) {
$product = Product::lockForUpdate()->findOrFail($item['product_id']);
if ($product->stock < $item['quantity']) {
throw new InsufficientStockException($product);
}
$product->decrement('stock', $item['quantity']);
$order->items()->create([
'product_id' => $product->id,
'quantity' => $item['quantity'],
'unit_price' => $product->price,
]);
}
$order->update(['total' => $order->items->sum(fn ($i) => $i->quantity * $i->unit_price)]);
return $order;
}, attempts: 3);
Manual Transaction Control
DB::beginTransaction();
try {
$order = Order::create([...]);
$order->items()->createMany([...]);
DB::commit();
} catch (\Exception $e) {
DB::rollBack();
throw $e;
}
Savepoints (Nested Transactions)
DB::transaction(function () {
Order::create([...]);
DB::transaction(function () {
Payment::create([...]);
});
});
Query Optimization
Eager Loading (Preventing N+1)
$orders = Order::all();
foreach ($orders as $order) {
echo $order->customer->name;
}
$orders = Order::with('customer')->get();
$orders = Order::with('customer', 'items.product')->get();
$orders = Order::with(['items' => function ($query) {
$query->where('quantity', '>', 1)->orderBy('unit_price', 'desc');
}])->get();
$orders = Order::all();
$orders->load('customer');
Prevent Lazy Loading in Development
public function boot(): void
{
Model::preventLazyLoading(! app()->isProduction());
}
This throws an exception whenever a relationship is lazy-loaded, forcing you to use
eager loading everywhere.
Subquery Selects
$users = User::query()
->addSelect([
'last_order_at' => Order::select('created_at')
->whereColumn('user_id', 'users.id')
->latest()
->take(1),
])
->get();
Aggregate Functions on Relationships
$orders = Order::withCount('items')->get();
echo $orders->first()->items_count;
$orders = Order::withSum('items', 'quantity')
->withAvg('items', 'unit_price')
->get();
echo $orders->first()->items_sum_quantity;
echo $orders->first()->items_avg_unit_price;
Chunking Large Datasets
Order::where('status', 'pending')->chunk(500, function ($orders) {
foreach ($orders as $order) {
$order->processReminder();
}
});
Order::where('status', 'pending')->lazy()->each(function ($order) {
$order->processReminder();
});
Order::where('status', 'pending')->chunkById(500, function ($orders) {
foreach ($orders as $order) {
$order->update(['reminded_at' => now()]);
}
});
When to Use Raw Queries
Use raw queries sparingly and only when Eloquent cannot express the logic efficiently:
$stats = DB::select(<<<'SQL'
SELECT
DATE(created_at) as date,
COUNT(*) as total_orders,
SUM(total) as revenue,
AVG(total) as avg_order_value
FROM orders
WHERE created_at >= ?
GROUP BY DATE(created_at)
ORDER BY date DESC
SQL, [now()->subDays(30)]);
$orders = Order::query()
->selectRaw('DATE(created_at) as date, COUNT(*) as count')
->groupByRaw('DATE(created_at)')
->get();
Database Query Logging
DB::enableQueryLog();
dd(DB::getQueryLog());
Useful Artisan Commands
php artisan migrate
php artisan migrate:rollback
php artisan migrate:fresh
php artisan migrate:fresh --seed
php artisan migrate:status
php artisan make:migration create_orders_table
php artisan make:model Order -mfscR