Skip to main content
database-design 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.
Ir a la instalación Skills Marketplace Descubre y explora habilidades de IA creadas por la comunidad.
Instalar con Codex o Claude Copia este prompt, pégalo en Codex, Claude u otro asistente, y deja que revise la página de la skill y la instale por ti.
Mostrar detalles del prompt Un comando directo omite el prompt de revisión. Revisa el origen antes de ejecutarlo.
npx skills add https://github.com/bramato/laravel-react-plugins --skill database-designEl comando permanece en una sola línea. Desplázate horizontalmente para revisarlo antes de copiarlo.
¿Prefieres una copia local? Descarga los archivos que SkillsMP tiene disponibles ahora.
Descargar Zip Descargando... Explorador de archivos
3 archivos Ocupaciones relacionadas SOC
Basado en la clasificación ocupacional SOC
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 aloneuser_id AND statususer_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
Records must be auditable (orders, invoices, financial data)
Records might need to be restored
Related data references the record and you need referential integrity
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
Design RESTful APIs with Laravel following best practices for resource naming, HTTP methods, status codes, pagination, filtering, error responses, and versioning. Use when creating API endpoints, designing response formats, implementing pagination or filtering, handling API errors, or versioning your API. Also covers Inertia route conventions for monolith apps. Triggers on API endpoint, REST, route design, pagination, filtering, error response, or CORS.
Implement authentication with Laravel Sanctum and authorization with Policies and Gates. Use when setting up login/registration, implementing API token auth, SPA cookie auth, creating policies, defining gates, or building role-based access control. Triggers on authentication, authorization, login, Sanctum, policy, gate, RBAC, permission, or middleware.
