Joins, Relationships, Keys, Normalization, and Practical Data Modeling

Relationships Are the Heart of Relational Databases

Relational databases become powerful when tables connect meaningfully. A customer has many orders. An order has many order items. A course has many students through an enrollment table. These relationships let you ask rich questions without duplicating the same information in multiple places.

Primary Keys and Foreign Keys

CREATE TABLE order_items (
    order_item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Primary keys uniquely identify rows. Foreign keys tie related records together and prevent broken references, such as an order item pointing to a product that does not exist.

Joining Tables to Produce Useful Results

SELECT
    o.order_id,
    c.full_name,
    o.order_status,
    o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_status = 'PENDING';

Joins answer real operational questions: which employee raised a request, which customer placed a payment, which department owns a project, or which student enrolled in a course. They are central to reporting and application data retrieval.

Normalization as a Discipline

Normalization helps avoid repeating the same data in many places. For example, customer contact information belongs in the customer table, not copied into every order row. Product prices may be copied into order items only when you intentionally want a historical snapshot. Good design depends on understanding when duplication is harmful and when it is required for audit or performance reasons.