Chapter 6

Working with Multiple Tables

Understand how tables connect and relate to each other to form powerful database systems.

🔗 The Power of Connected Data

In Chapter 5, you learned about individual tables. But in the real world, data is rarely stored in just one table. Instead, it's organized across multiple related tables that work together.

🎯 Real-Life Analogy: School Filing System

Imagine a school's filing cabinets:

  • Cabinet 1: Student information (ID, name, address)
  • Cabinet 2: Class enrollments (which student is in which class)
  • Cabinet 3: Course information (course name, teacher, room number)

These cabinets are separate, but they're connected by student IDs and course IDs. You can start with a student and find all their classes, or start with a class and find all enrolled students.

That's exactly how database tables work!

💡 What You'll Learn

By the end of this chapter, you'll be able to:

  • Understand why data is split across multiple tables
  • Recognize how tables connect through common fields
  • Identify different types of relationships (one-to-one, one-to-many, many-to-many)
  • Read and follow connections between related tables
  • Appreciate the benefits of proper database structure

🤔 Why Not Just Use One Big Table?

Great question! Let's see what happens when we try to put everything in one table.

❌ One Giant Table (Bad Approach)

Student ID Student Name Course Name Teacher Room
101 Sarah Math Mr. Smith Room 201
101 Sarah Science Dr. Jones Room 305
101 Sarah English Ms. Lee Room 102
102 Mike Math Mr. Smith Room 201

Problems:

  • Sarah's name is repeated 3 times (redundant!)
  • Math info is repeated for every student
  • If Sarah changes her name, we must update 3 rows
  • If Math room changes, we update many rows
  • Lots of wasted space and risk of errors

✅ Multiple Related Tables (Good Approach)

Students Table:

Student ID Name
101 Sarah
102 Mike

Courses Table:

Course ID Course Name Teacher Room
C1 Math Mr. Smith 201
C2 Science Dr. Jones 305

Enrollments Table:

Student ID Course ID
101 C1
101 C2
102 C1

Benefits:

  • No repetition—each name appears once
  • Easy updates—change Sarah's name in one place
  • Less storage space
  • No inconsistencies or errors
  • Flexible—easy to add new students or courses

🎯 Key Principle: Avoid Redundancy

Each piece of information should be stored in exactly ONE place.

This principle is called normalization in database design. It prevents:

  • Data inconsistencies (Sarah's name spelled differently in different rows)
  • Update anomalies (forgetting to update all copies)
  • Wasted storage space
  • Maintenance nightmares

⚡ Quick Check: Why Multiple Tables?

Test your understanding of why we use multiple tables:

1. Using multiple related tables reduces data redundancy (repetition).

2. Using one big table makes it easier to update information.

3. Normalization is the principle of avoiding redundancy by storing each piece of data in one place.

🔗 How Tables Connect: Related Tables

Tables connect through common fields—columns that appear in multiple tables and allow you to link records together.

Students

Student ID
Name
Email

Connected by Student ID

Enrollments

Student ID
Course ID
Semester

Understanding Foreign Keys

When a column in one table refers to the primary key of another table, it's called a foreign key.

Example Breakdown:

  • Students table: Student ID is the Primary Key
  • Enrollments table: Student ID is a Foreign Key (refers to Students table)
  • Courses table: Course ID is the Primary Key
  • Enrollments table: Course ID is a Foreign Key (refers to Courses table)

Students Table

Student ID 🔑 Name Grade
101 Sarah Johnson 11
102 Mike Chen 11
103 Emma Davis 12

🔑 = Primary Key

Courses Table

Course ID 🔑 Course Name Teacher
C101 Algebra II Mr. Smith
C102 Chemistry Dr. Jones
C103 English Lit Ms. Lee

🔑 = Primary Key

Enrollments Table (Bridge Table)

Enrollment ID 🔑 Student ID 🔗 Course ID 🔗 Semester Grade
1 101 C101 Fall 2024 A
2 101 C102 Fall 2024 B+
3 102 C101 Fall 2024 A-
4 103 C103 Fall 2024 A

🔗 = Foreign Key (references another table)

🔍 Reading This Example:

Look at Enrollment ID #1:

  1. Student ID 101 → Look up in Students table → Find "Sarah Johnson"
  2. Course ID C101 → Look up in Courses table → Find "Algebra II taught by Mr. Smith"
  3. Conclusion: Sarah Johnson is enrolled in Algebra II and got an A

This is how we "join" data from multiple tables to tell a complete story!

▼ 🧒 Kid-Friendly Explanation: What are Foreign Keys?

Think of foreign keys like phone numbers in your contacts:

Imagine you have a notebook where you write down your friends' names and information. That's like your "Students table."

Now imagine you have ANOTHER notebook where you track which friends are coming to your birthday party. You don't write all their information again (address, phone number, etc.) - you just write their NAME to refer back to your first notebook!

That's exactly what a foreign key does!

  • 🔑 Primary Key = The main ID number for each student (like their locker number - unique to them!)
  • 🔗 Foreign Key = Using that same ID number in another table to connect back to the student

Instead of writing "Sarah Johnson, Grade 11, email@example.com" every time, you just write her Student ID (101) and look up the rest when you need it!

✍️ Fill in the Blanks: Primary Keys and Foreign Keys

Complete the sentences about how tables connect:

Word Bank:

primary key foreign key unique connects Enrollments

1. A uniquely identifies each record in a table.

2. In the Students table, Student ID must be for every student.

3. A in one table refers to the primary key of another table.

4. The foreign key two tables together.

5. In our school example, is the bridge table connecting Students and Courses.

🔀 Types of Relationships

Tables can relate to each other in three main ways:

1️⃣ One-to-One Relationship

Definition: One record in Table A relates to exactly one record in Table B.

Students

Student ID: 101
Name: Sarah

1:1

Student Lockers

Student ID: 101
Locker #: 205

Real-World Examples of One-to-One:

  • Person → Passport (one person has one passport)
  • Employee → Employee ID Badge
  • Student → Locker
  • Country → Capital City

2️⃣ One-to-Many Relationship

Definition: One record in Table A can relate to many records in Table B.

Teachers

Teacher ID: T1
Name: Mr. Smith

1:Many

Students

Student 1
Student 2
Student 3
...30 students

Real-World Examples of One-to-Many:

  • Teacher → Students (one teacher teaches many students)
  • Customer → Orders (one customer places many orders)
  • Author → Books (one author writes many books)
  • Department → Employees
  • Category → Products

This is the MOST COMMON relationship type!

3️⃣ Many-to-Many Relationship

Definition: Many records in Table A can relate to many records in Table B.

Students

Sarah
Mike
Emma

Many:Many

Courses

Math
Science
English

Important: Many-to-many relationships require a bridge table (also called junction table or associative table)!

Students

Student ID Name
101 Sarah
102 Mike

Enrollments (Bridge)

Student ID Course ID
101 C1
101 C2
102 C1

Courses

Course ID Name
C1 Math
C2 Science

Real-World Examples of Many-to-Many:

  • Students ↔ Courses (students take many courses; courses have many students)
  • Actors ↔ Movies (actors appear in many movies; movies have many actors)
  • Products ↔ Orders (products appear in many orders; orders contain many products)
  • Tags ↔ Blog Posts (posts have many tags; tags apply to many posts)

🔑 Quick Reference: Relationship Types

Relationship Description Example Bridge Table?
One-to-One 1 ↔ 1 Person → Passport No
One-to-Many 1 ↔ Many Teacher → Students No
Many-to-Many Many ↔ Many Students ↔ Courses Yes (Required!)

🎯 Practice: Match Examples to Relationship Types

Drag each real-world example to the correct relationship type:

🛂 Employee → ID Badge
👩‍🏫 Teacher → Students
🎬 Actors ↔ Movies
🌍 Country → Capital City
📚 Category → Products
📝 Students ↔ Courses
1️⃣ One-to-One (1:1)
Each record matches exactly one other
2️⃣ One-to-Many (1:Many)
One record relates to many
3️⃣ Many-to-Many (Many:Many)
Many relate to many (needs bridge table)

📖 Reading Related Tables

Let's practice following connections across multiple tables!

Step-by-Step: Finding All Courses for a Student

Question: What courses is Sarah (Student ID: 101) taking?

1. Start with Students Table

Find Student ID 101 → Sarah Johnson

Students:
101 | Sarah Johnson | Grade 11

2. Look up in Enrollments Table

Find all rows where Student ID = 101

Enrollments:
1 | 101 | C101 | Fall 2024 | A
2 | 101 | C102 | Fall 2024 | B+

Found 2 enrollments: C101 and C102

3. Look up in Courses Table

Find courses C101 and C102

Courses:
C101 | Algebra II    | Mr. Smith
C102 | Chemistry     | Dr. Jones

4. Combine the Information

Answer: Sarah Johnson is taking:

  • Algebra II with Mr. Smith (Grade: A)
  • Chemistry with Dr. Jones (Grade: B+)

Reverse Direction: Finding All Students in a Course

Question: Which students are in Algebra II (Course ID: C101)?

1. Start with Courses Table

Find Course ID C101 → Algebra II

2. Look up in Enrollments Table

Find all rows where Course ID = C101

Enrollments:
1 | 101 | C101 | Fall 2024 | A
3 | 102 | C101 | Fall 2024 | A-

Found Student IDs: 101 and 102

3. Look up in Students Table

Find students 101 and 102

Students:
101 | Sarah Johnson
102 | Mike Chen

4. Answer

Students in Algebra II:

  • Sarah Johnson (Grade: A)
  • Mike Chen (Grade: A-)

🎮 Interactive: Follow the Connections

Click on a student to see their enrolled courses:

Select a student to see their courses

📋 Practice: Order the Steps to Find a Student's Courses

Arrange these steps in the correct order to find all courses for a student:

Step 1: Start with the Students table and find the student by their Student ID
Step 2: Use the Student ID to find matching rows in the Enrollments table
Step 3: Get the Course IDs from the Enrollments table
Step 4: Look up each Course ID in the Courses table to get course details

✨ Benefits of Proper Structure

Why go through all this trouble of splitting data across multiple tables?

1. Avoid Repetition

Each piece of data is stored once.

Example: Sarah's name appears once in Students table, not repeated in every enrollment.

2. Easy Updates

Change data in one place, and it's updated everywhere.

Example: If a course room changes, update one row in Courses table.

3. Data Consistency

No risk of conflicting information.

Example: Can't have Sarah's name spelled differently in different places.

4. Scalability

Easy to add new records without restructuring.

Example: Add 100 new students without changing table structure.

5. Flexibility

Can query data in multiple ways.

Example: Find all courses for a student OR all students in a course.

6. Storage Efficiency

Less redundancy = less storage space.

Example: Store "Algebra II" once, not once per student.

Without Proper Structure

Scenario: Math class moves from Room 201 to Room 305

One giant table: Must update 30 rows (one per student)

Risk: Miss a row → inconsistent data

Time: Error-prone and slow

With Proper Structure

Scenario: Math class moves from Room 201 to Room 305

Multiple related tables: Update 1 row in Courses table

Risk: None—change happens once

Time: Fast and reliable

🎯 Practice Exercises

Exercise 1: Identify the Relationship Type

For each scenario, identify if it's One-to-One, One-to-Many, or Many-to-Many:

  1. Library → Books (one library has many books)
  2. Employee → Desk (one employee has one assigned desk)
  3. Movies → Actors (movies have many actors; actors appear in many movies)
  4. Department → Manager (one department has one manager)
  5. Products → Categories (products belong to many categories; categories contain many products)
Show Answers
  1. One-to-Many (one library → many books)
  2. One-to-One (one employee → one desk)
  3. Many-to-Many (needs bridge table)
  4. One-to-One (one department → one manager)
  5. Many-to-Many (needs bridge table)

Exercise 2: Design Tables

Scenario: Design a database for a library system that tracks:

  • Books (title, author, ISBN)
  • Members (name, member ID, email)
  • Checkouts (which member has which book, due date)

Questions:

  1. How many tables do you need?
  2. What are the primary keys?
  3. What are the foreign keys?
  4. What type of relationship is Members ↔ Books?
Show Answers
  1. 3 tables: Books, Members, Checkouts
  2. Primary keys:
    • Books: ISBN
    • Members: Member ID
    • Checkouts: Checkout ID (or composite key of Member ID + ISBN)
  3. Foreign keys in Checkouts table:
    • Member ID (refers to Members)
    • ISBN (refers to Books)
  4. Many-to-Many: Members can check out many books; books can be checked out by many members (over time). Checkouts is the bridge table.

📝 Knowledge Check

1. Why is it better to use multiple related tables instead of one big table?

2. What is a foreign key?

3. In a one-to-many relationship between Teachers and Students, what does this mean?

4. What is a bridge table (junction table)?

5. Students and Courses have what type of relationship?

6. What is the main benefit of avoiding data redundancy?

7. In the school database example, which table is the bridge table?

8. To find all courses a specific student is taking, you would:

9. What is the main purpose of a primary key in a table?

10. In a one-to-one relationship between Person and Passport tables, what is true?

← Previous: Understanding Tables Next: Analytics Mindset →