Lesson 38 of 40
Data Access
Intermediate
45 min
Database Design & Migrations
Design robust database schemas, write zero-downtime migrations, handle multi-tenancy patterns, and optimize indexes for production.
Part 1: Zero-Downtime Migrations
| Step | Action | Safe? |
|---|---|---|
| 1 | Add nullable column | ✅ Online |
| 2 | Deploy app using new column | ✅ |
| 3 | Backfill column data | ✅ Batched |
| 4 | Add NOT NULL constraint | ✅ After backfill |
| 5 | Remove old column | ✅ After old code removed |
Part 2: Index Strategy
// EF Core index configuration
modelBuilder.Entity<Order>()
.HasIndex(o => new { o.CustomerId, o.Status })
.HasFilter("[Status] != 'Completed'") // Partial index
.HasDatabaseName("IX_Orders_Customer_ActiveStatus");
modelBuilder.Entity<Order>()
.HasIndex(o => new { o.CustomerId, o.Status })
.HasFilter("[Status] != 'Completed'") // Partial index
.HasDatabaseName("IX_Orders_Customer_ActiveStatus");
Part 3: Multi-Tenancy Patterns
| Pattern | Isolation | Cost |
|---|---|---|
| Row-level (TenantId column) | Low | Low |
| Schema-per-tenant | Medium | Medium |
| Database-per-tenant | High | High |
Part 4: Migration Squashing
After many migrations, squash them for performance:
# Remove all migration files
# Create a single "Initial" migration from current schema
dotnet ef migrations add InitialCreate --output-dir Data/Migrations
# Mark existing databases as already applied
dotnet ef database update InitialCreate
# Create a single "Initial" migration from current schema
dotnet ef migrations add InitialCreate --output-dir Data/Migrations
# Mark existing databases as already applied
dotnet ef database update InitialCreate
