Session 03 - Data Modeling Strategies

Database Paradigm Comparison

Khi nào chọn gì?

Normalization vs Denormalization

Normalization (1NF → 3NF)

Ví dụ: Normalized Design

sql
-- 3NF Design
CREATE TABLE Customers (
    Id INT PRIMARY KEY,
    Name NVARCHAR(200) NOT NULL,
    Email NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders (
    Id INT PRIMARY KEY,
    CustomerId INT FOREIGN KEY REFERENCES Customers(Id),
    OrderDate DATETIME2 NOT NULL,
    Status NVARCHAR(50) NOT NULL
);

CREATE TABLE OrderItems (
    Id INT PRIMARY KEY,
    OrderId INT FOREIGN KEY REFERENCES Orders(Id),
    ProductId INT FOREIGN KEY REFERENCES Products(Id),
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(18,2) NOT NULL
);

CREATE TABLE Products (
    Id INT PRIMARY KEY,
    Name NVARCHAR(200) NOT NULL,
    CategoryId INT FOREIGN KEY REFERENCES Categories(Id),
    Price DECIMAL(18,2) NOT NULL
);

Denormalized Design (cho read performance)

sql
-- Denormalized read model - tránh JOINs
CREATE TABLE OrderSummary (
    OrderId INT PRIMARY KEY,
    CustomerName NVARCHAR(200),    -- Denormalized từ Customers
    OrderDate DATETIME2,
    Status NVARCHAR(50),
    TotalAmount DECIMAL(18,2),     -- Pre-calculated
    ItemCount INT,                  -- Pre-calculated
    TopProductName NVARCHAR(200)   -- Denormalized từ Products
);

-- Maintain bằng trigger hoặc application logic

Trade-offs Table

Event Sourcing Data Model

Concept

Thay vì lưu current state, lưu sequence of events đã xảy ra.

Implementation

csharp
// Event base class
public abstract record DomainEvent
{
    public Guid EventId { get; init; } = Guid.NewGuid();
    public DateTimeOffset Timestamp { get; init; } = DateTimeOffset.UtcNow;
    public required string AggregateId { get; init; }
    public int Version { get; init; }
}

// Specific events
public record OrderCreated : DomainEvent
{
    public required string CustomerId { get; init; }
    public required decimal TotalAmount { get; init; }
}

public record OrderItemAdded : DomainEvent
{
    public required string ProductId { get; init; }
    public required int Quantity { get; init; }
    public required decimal UnitPrice { get; init; }
}

public record OrderPaymentReceived : DomainEvent
{
    public required decimal Amount { get; init; }
    public required string PaymentMethod { get; init; }
}

// Event Store table
// CREATE TABLE EventStore (
//     EventId UNIQUEIDENTIFIER PRIMARY KEY,
//     AggregateId NVARCHAR(100) NOT NULL,
//     AggregateType NVARCHAR(100) NOT NULL,
//     Version INT NOT NULL,
//     EventType NVARCHAR(200) NOT NULL,
//     Data NVARCHAR(MAX) NOT NULL,    -- JSON serialized event
//     Timestamp DATETIME2 NOT NULL,
//     UNIQUE(AggregateId, Version)     -- Optimistic concurrency
// );

// Aggregate reconstruction
public class Order
{
    public string Id { get; private set; } = string.Empty;
    public string CustomerId { get; private set; } = string.Empty;
    public OrderStatus Status { get; private set; }
    public List<OrderItem> Items { get; } = new();
    public decimal TotalAmount { get; private set; }
    public int Version { get; private set; }

    // Replay events to rebuild state
    public void Apply(DomainEvent @event)
    {
        switch (@event)
        {
            case OrderCreated e:
                Id = e.AggregateId;
                CustomerId = e.CustomerId;
                TotalAmount = e.TotalAmount;
                Status = OrderStatus.Created;
                break;
            case OrderItemAdded e:
                Items.Add(new OrderItem(e.ProductId, e.Quantity, e.UnitPrice));
                break;
            case OrderPaymentReceived:
                Status = OrderStatus.Paid;
                break;
        }
        Version = @event.Version;
    }
}

CQRS - Command Query Responsibility Segregation

Architecture

Implementation

csharp
// Command side - full validation, business rules
public class CreateOrderCommand : IRequest<OrderResult>
{
    public required string CustomerId { get; init; }
    public required List<OrderItemDto> Items { get; init; }
}

public class CreateOrderHandler : IRequestHandler<CreateOrderCommand, OrderResult>
{
    private readonly AppDbContext _writeDb;
    private readonly IEventBus _eventBus;

    public async Task<OrderResult> Handle(CreateOrderCommand cmd, CancellationToken ct)
    {
        // Business logic, validation, write to normalized DB
        var order = Order.Create(cmd.CustomerId, cmd.Items);
        _writeDb.Orders.Add(order);
        await _writeDb.SaveChangesAsync(ct);

        // Publish event for read model update
        await _eventBus.PublishAsync(new OrderCreatedEvent(order));

        return new OrderResult(order.Id);
    }
}

// Query side - optimized for reads, no business logic
public class GetOrderSummaryQuery : IRequest<OrderSummaryDto>
{
    public required string OrderId { get; init; }
}

public class GetOrderSummaryHandler : IRequestHandler<GetOrderSummaryQuery, OrderSummaryDto?>
{
    private readonly IDistributedCache _cache;
    private readonly CosmosClient _cosmosClient;

    public async Task<OrderSummaryDto?> Handle(GetOrderSummaryQuery query, CancellationToken ct)
    {
        // Read from denormalized read model - fast, no JOINs
        var container = _cosmosClient.GetContainer("ReadDb", "OrderSummaries");
        var response = await container.ReadItemAsync<OrderSummaryDto>(
            query.OrderId, new PartitionKey(query.OrderId), cancellationToken: ct);
        return response.Resource;
    }
}

Polyglot Persistence

Concept

Dùng database phù hợp nhất cho từng use case thay vì ép mọi thứ vào một database.

Challenges

Trade-offs Summary