Database Models & Schema

Core E-Commerce Relationships

Core relationships showing product catalog, orders, and user interactions

Automotive & Content Modules

Automotive parts, services, and content management relationships

Catalog Module (19 Tables)

Product management, categories, brands, tags, reviews, wishlist, and comparison features.

Orders Module (13 Tables)

Shopping cart, orders, payments, shipments, and discounts.

Automotive Module (9 Tables)

Vehicle management, parts catalog, compatibility, and services.

Blog Module (4 Tables)

Blog posts, categories, comments, and tagging.

CMS Module (6 Tables)

Content pages, menus, sliders, FAQs, and videos.

Notifications Module (3 Tables)

User notifications, templates, and settings.

Identity & User Module (3 Tables)

User profiles, addresses, and authentication tracking.

System Module (2 Tables)

Configuration, file storage, and audit logging.

Strategic Indexing Strategy

The database includes 46+ carefully placed indexes to optimize query performance. Here are the key indexing patterns:

Performance Impact

Product Queries

Get Active Products with Brand and Category

// EF Core LINQ Query
var products = await _context.Products
    .Include(p => p.Brand)
    .Include(p => p.ProductCategories)
        .ThenInclude(pc => pc.Category)
    .Where(p => p.IsActive)
    .OrderByDescending(p => p.CreationTime)
    .Take(20)
    .AsNoTracking()
    .ToListAsync();

// Uses indexes: Product.IsActive, Product.CreationTime

Search Products by Name or Code

// Search query with multiple filters
var results = await _context.Products
    .Where(p => p.IsActive &&
           (p.Name.Contains(searchTerm) ||
            p.Code.Contains(searchTerm)))
    .OrderBy(p => p.Name)
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        Code = p.Code,
        Price = p.Price,
        BrandName = p.Brand.Name
    })
    .ToListAsync();

// Uses indexes: Product.IsActive, Product.Code

Order Queries

Get User Orders with Items

// Get complete order with items
var order = await _context.Orders
    .Include(o => o.Items)
    .Include(o => o.StatusHistory)
        .OrderByDescending(h => h.ChangedAt)
    .FirstOrDefaultAsync(o => o.OrderNumber == orderNumber);

// Uses indexes: Order.OrderNumber (unique)

Get Orders by Status and Date Range

// Filter orders by status and date
var orders = await _context.Orders
    .Where(o => o.Status == OrderStatus.Processing &&
                o.CreationTime >= startDate &&
                o.CreationTime <= endDate)
    .OrderByDescending(o => o.CreationTime)
    .ToListAsync();

// Uses indexes: Order.Status, Order.CreationTime

Review Queries

Get Approved Product Reviews with Replies

// Get reviews with relationships
var reviews = await _context.ProductReviews
    .Include(r => r.Replies)
    .Include(r => r.Images)
    .Include(r => r.Likes)
    .Where(r => r.ProductId == productId && r.IsApproved)
    .OrderByDescending(r => r.CreationTime)
    .ToListAsync();

// Uses indexes: ProductReview.ProductId, ProductReview.IsApproved

Performance Query Tips

• Always use .AsNoTracking() for read-only queries
• Use projections (Select) to retrieve only needed columns
• Leverage indexes by filtering on indexed columns first
• Use Include strategically to avoid N+1 query problems
• Apply pagination with Skip/Take for large result sets
• Use composite indexes for multi-column WHERE clauses
• Cache frequently accessed data in Redis (2-hour TTL for products)
• Monitor query performance with SQL Server Profiler

Database Migrations (20+ Applied)

The database schema has evolved through 20+ code-first migrations: