Joins, Relationships, Constraints, Normalization, and Data Modeling

Why Relationships Matter

Relational databases become powerful when tables can describe meaningful relationships. Customers place orders. Orders contain order items. Employees belong to departments. Students register for courses. Instead of copying the same text into many rows, SQL Server lets you store data once and connect it through keys.

Primary Keys and Foreign Keys

CREATE TABLE dbo.OrderItems (
    OrderItemId BIGINT IDENTITY(1,1) PRIMARY KEY,
    OrderId BIGINT NOT NULL,
    ProductId BIGINT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderId) REFERENCES dbo.Orders(OrderId),
    CONSTRAINT FK_OrderItems_Products
        FOREIGN KEY (ProductId) REFERENCES dbo.Products(ProductId)
);

Foreign keys enforce referential integrity so related records cannot silently break.

Joining Tables for Useful Results

SELECT
    o.OrderId,
    c.FullName,
    o.OrderStatus,
    o.OrderDate
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerId = o.CustomerId
WHERE o.OrderStatus = 'PENDING';

Joins are used constantly in reports, admin tools, analytics, and application services. Deep join understanding is one of the clearest signs that a student has moved beyond basic SQL.

Normalization and Tradeoffs

Normalization helps keep data consistent by reducing unnecessary duplication. Customer contact details belong in the customer table, not copied into each order row. At the same time, some duplication can be intentional, such as storing item price in an order line to preserve historical billing accuracy. Advanced design means understanding those tradeoffs rather than blindly following rules.