Module 8 — Database Relationships

1. The Problem: Data Redundancy

Imagine we are building a blog. If we store everything in one table, it looks like this:

If Alice changes her bio, we have to update 3 rows. If she has 1,000 posts, we have to update 1,000 rows. This is Redundancy, and it leads to bugs.

2. The Solution: Normalization

Normalization is the process of organizing data to minimize duplication. We split the "Big Table" into smaller, related tables.

The 3 Stages of Normalization (Simplified)

1. 1st Normal Form (1NF): Every cell must contain only one value. No lists inside cells.
2. 2nd Normal Form (2NF): Every non-key column must depend on the entire Primary Key.
3. 3rd Normal Form (3NF): Remove columns that depend on other non-key columns (e.g., if you have zip_code, you don't need city because the zip code already tells you the city).

3. Connecting Tables: PK and FK

To link our new tables, we use Keys.

• Primary Key (PK): The unique ID of a row in its own table.
• Foreign Key (FK): A column that points to a PK in another table.

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);
 
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    author_id INTEGER REFERENCES authors(id) -- This is the Foreign Key
);

4. The 3 Types of Relationships

A. One-to-One (1:1)

One record in Table A matches exactly one record in Table B.

• Example: A User has one User_Profile.
• Why? To keep sensitive data (like passwords/SSNs) in a separate table from public profile info.

B. One-to-Many (1:N) — Most Common

One record in Table A matches many records in Table B.

• Example: One Author has many Posts. One Category has many Products.

C. Many-to-Many (M:N)

Many records in Table A match many records in Table B.

• Example: A Student can take many Courses, and a Course has many Students.
• The Junction Table: You cannot do this with two tables. You need a 3rd "link" table.

Entity Relationship Diagram

-- The 'enrollments' table links students and courses
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);

Blog Database Schema

Entity Relationship Diagram (Student-Course Example)

Practice Exercise

1. Design a schema for an "Order Tracking" system:
   • What tables do you need? (customers, orders, products).
   • What are the relationships? (One customer → Many orders).
   • Draw it on paper or in a text file.
2. Create two tables in your database: instructors and lessons.
   • Link lessons to instructors using a Foreign Key.
   • Try to insert a lesson with an instructor_id that doesn't exist. See what happens!