7.5 Python Database Design, Relationships & Joins

7.5 Python Database Design, Relationships & Joins

Real-world applications rarely store all information in a single database table. Instead, related information is divided into multiple tables and connected through keys and relationships.

Understanding database design and SQL joins is essential when building Python applications such as school ERP systems, inventory software, customer-management systems, reporting tools, and data-analysis pipelines.

1. Learning Objectives

By the end of this lesson, you will be able to:

• Understand relational database design.
• Explain primary keys and foreign keys.
• Design related database tables.
• Understand one-to-one relationships.
• Understand one-to-many relationships.
• Understand many-to-many relationships.
• Use INNER JOIN.
• Use LEFT JOIN.
• Understand RIGHT JOIN.
• Use multiple joins.
• Join tables using Python.
• Use joins with pandas.
• Avoid duplicate and ambiguous results.
• Build a practical relational database model.

2. Why Multiple Tables?

Consider a school system containing student information, class information, subjects, and examination marks.

Storing everything in one table can result in repeated information.

Student Name
Class
Class Teacher
Subject
Subject Teacher
Marks
Exam Name

A relational database separates these entities into appropriate tables and connects them using keys.

Students
    |
    +---- Classes
    |
    +---- Enrollments
             |
             +---- Subjects
             |
             +---- Marks

3. What Is a Primary Key?

A primary key uniquely identifies each record in a table.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150)
);

In this example, id uniquely identifies each student.

4. Primary Key Characteristics

• Uniquely identifies a record.
• Should not contain duplicate values.
• Cannot normally be NULL.
• Provides a stable reference to a record.
• Can be referenced by other tables.

5. What Is a Foreign Key?

A foreign key connects a column in one table to a key in another table.

CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(50) NOT NULL
);


CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    class_id INT,

    FOREIGN KEY (class_id)
        REFERENCES classes(id)
);

Here, students.class_id references classes.id.

6. Primary Key vs Foreign Key

7. One-to-Many Relationship

A one-to-many relationship means one record in one table can be associated with many records in another table.

One Class
    |
    +---- Student 1
    |
    +---- Student 2
    |
    +---- Student 3
    |
    +---- Student 4

This is one of the most common relationships in application databases.

8. Example — Classes and Students

CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(50) NOT NULL
);


CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    class_id INT NOT NULL,

    FOREIGN KEY (class_id)
        REFERENCES classes(id)
);

9. Insert Related Data

cursor.execute(
    """
    INSERT INTO classes
    (class_name)
    VALUES (%s)
    """,
    ("Class XII A",)
)

class_id = cursor.lastrowid


cursor.execute(
    """
    INSERT INTO students
    (name, class_id)
    VALUES (%s, %s)
    """,
    (
        "Aarav",
        class_id
    )
)

connection.commit()

10. INNER JOIN

INNER JOIN returns records where matching values exist in both tables.

SELECT
    students.name,
    classes.class_name
FROM students
INNER JOIN classes
    ON students.class_id = classes.id;

11. INNER JOIN Result

12. INNER JOIN with Python

cursor.execute(
    """
    SELECT
        students.id,
        students.name,
        classes.class_name
    FROM students
    INNER JOIN classes
        ON students.class_id = classes.id
    ORDER BY students.name
    """
)


rows = cursor.fetchall()


for row in rows:

    print(row)

13. Using Table Aliases

Aliases make long SQL queries easier to read.

SELECT
    s.name,
    c.class_name
FROM students AS s
INNER JOIN classes AS c
    ON s.class_id = c.id;

14. LEFT JOIN

A LEFT JOIN returns every record from the left table and matching records from the right table.

SELECT
    c.class_name,
    s.name
FROM classes AS c
LEFT JOIN students AS s
    ON c.id = s.class_id;

A class with no students can still appear in the result.

15. Finding Records Without Matches

A common use of LEFT JOIN is identifying records that have no related record.

SELECT
    c.id,
    c.class_name
FROM classes AS c
LEFT JOIN students AS s
    ON c.id = s.class_id
WHERE s.id IS NULL;

This query identifies classes that currently have no students.

16. RIGHT JOIN

A RIGHT JOIN returns every record from the right table and matching records from the left table.

SELECT
    s.name,
    c.class_name
FROM students AS s
RIGHT JOIN classes AS c
    ON s.class_id = c.id;

In practice, many developers rewrite RIGHT JOIN queries as equivalent LEFT JOIN queries by changing the table order, which can make complex SQL easier to read.

17. CROSS JOIN

A CROSS JOIN produces combinations between rows of two tables.

SELECT
    s.name,
    sub.subject_name
FROM students AS s
CROSS JOIN subjects AS sub;

If there are 10 students and 5 subjects, the result can contain 50 combinations.

18. SELF JOIN

A self join joins a table to itself.

It is useful for hierarchical data such as employees and managers.

SELECT
    employee.name AS employee,
    manager.name AS manager
FROM employees AS employee
LEFT JOIN employees AS manager
    ON employee.manager_id = manager.id;

19. One-to-One Relationship

A one-to-one relationship means one record in one table is associated with at most one record in another table.

Student
   |
   +---- Student Profile

Employee
   |
   +---- Employee Account

20. One-to-One Example

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL
);


CREATE TABLE employee_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT UNIQUE NOT NULL,
    address VARCHAR(250),

    FOREIGN KEY (employee_id)
        REFERENCES employees(id)
);

The UNIQUE constraint on employee_id prevents multiple profiles from being associated with the same employee.

21. Many-to-Many Relationship

A many-to-many relationship occurs when many records in one table can be related to many records in another table.

Students
   |
   +---- Mathematics
   |
   +---- Physics
   |
   +---- Computer Science


Subjects
   |
   +---- Many Students

Relational databases normally implement this relationship using a junction or bridge table.

22. Junction Table

students
----------------
id
name


subjects
----------------
id
subject_name


student_subjects
----------------
student_id
subject_id

23. Create a Many-to-Many Structure

CREATE TABLE subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL
);


CREATE TABLE student_subjects (
    student_id INT NOT NULL,
    subject_id INT NOT NULL,

    PRIMARY KEY (
        student_id,
        subject_id
    ),

    FOREIGN KEY (student_id)
        REFERENCES students(id),

    FOREIGN KEY (subject_id)
        REFERENCES subjects(id)
);

24. Insert a Student-Subject Relationship

cursor.execute(
    """
    INSERT INTO student_subjects
    (student_id, subject_id)
    VALUES (%s, %s)
    """,
    (
        1,
        3
    )
)

connection.commit()

25. Query Many-to-Many Data

SELECT
    s.name,
    sub.subject_name
FROM students AS s
INNER JOIN student_subjects AS ss
    ON s.id = ss.student_id
INNER JOIN subjects AS sub
    ON ss.subject_id = sub.id
ORDER BY s.name;

26. Three-Table JOIN

Multiple joins can combine information from several related tables.

SELECT
    s.name,
    c.class_name,
    sub.subject_name
FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id

INNER JOIN student_subjects AS ss
    ON s.id = ss.student_id

INNER JOIN subjects AS sub
    ON ss.subject_id = sub.id;

27. JOIN with WHERE

SELECT
    s.name,
    c.class_name
FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id

WHERE c.class_name = %s;

Python can supply the class name safely as a parameter.

cursor.execute(
    """
    SELECT
        s.name,
        c.class_name
    FROM students AS s
    INNER JOIN classes AS c
        ON s.class_id = c.id
    WHERE c.class_name = %s
    """,
    (
        "Class XII A",
    )
)

28. JOIN with ORDER BY

SELECT
    s.name,
    c.class_name
FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id

ORDER BY
    c.class_name,
    s.name;

29. JOIN with GROUP BY

Joins can also be combined with aggregation.

SELECT
    c.class_name,
    COUNT(s.id) AS student_count
FROM classes AS c

LEFT JOIN students AS s
    ON c.id = s.class_id

GROUP BY
    c.id,
    c.class_name;

30. School Example — Student Count

This type of joined aggregation is useful for school dashboards and administrative reporting.

31. JOIN with HAVING

SELECT
    c.class_name,
    COUNT(s.id) AS student_count
FROM classes AS c

LEFT JOIN students AS s
    ON c.id = s.class_id

GROUP BY
    c.id,
    c.class_name

HAVING COUNT(s.id) > 30;

32. JOIN with Aggregate Functions

SELECT
    c.class_name,
    COUNT(s.id) AS students,
    AVG(s.age) AS average_age
FROM classes AS c

LEFT JOIN students AS s
    ON c.id = s.class_id

GROUP BY
    c.id,
    c.class_name;

33. JOIN and NULL Values

When a LEFT JOIN does not find a matching row, columns from the right table can contain NULL.

SELECT
    c.class_name,
    s.name
FROM classes AS c

LEFT JOIN students AS s
    ON c.id = s.class_id;

An unmatched class may appear with a NULL student name.

34. Handling NULL in Python

for row in rows:

    class_name = row[0]
    student_name = row[1]

    if student_name is None:

        print(
            class_name,
            "has no students"
        )

    else:

        print(
            class_name,
            student_name
        )

35. Avoiding Ambiguous Column Names

Multiple tables may contain columns with the same name.

students.id
classes.id

Explicit table aliases make the query clearer.

SELECT
    s.id AS student_id,
    c.id AS class_id,
    s.name,
    c.class_name
FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id;

36. JOIN and Column Aliases

SELECT
    s.name AS student_name,
    c.class_name AS class_name
FROM students AS s

INNER JOIN classes AS c
    ON s.class_id = c.id;

Aliases are especially useful when results are later converted into dictionaries or pandas DataFrames.

37. Reading JOIN Results into Python

cursor.execute(
    """
    SELECT
        s.name AS student_name,
        c.class_name
    FROM students AS s
    INNER JOIN classes AS c
        ON s.class_id = c.id
    """
)

rows = cursor.fetchall()


for student_name, class_name in rows:

    print(
        student_name,
        "->",
        class_name
    )

38. Using Dictionary Cursors

MySQL Connector/Python can return rows as dictionaries, which can make application code more readable.

cursor = connection.cursor(
    dictionary=True
)


cursor.execute(
    """
    SELECT
        s.name AS student_name,
        c.class_name
    FROM students AS s
    INNER JOIN classes AS c
        ON s.class_id = c.id
    """
)


rows = cursor.fetchall()


for row in rows:

    print(
        row["student_name"],
        row["class_name"]
    )

39. JOIN Results with pandas

import pandas as pd


query = """
SELECT
    s.name AS student_name,
    c.class_name
FROM students AS s
INNER JOIN classes AS c
    ON s.class_id = c.id
"""


df = pd.read_sql(
    query,
    connection
)


print(df)

40. Filtering a JOIN Using Python Parameters

query = """
SELECT
    s.name AS student_name,
    c.class_name
FROM students AS s
INNER JOIN classes AS c
    ON s.class_id = c.id
WHERE c.class_name = %s
"""


df = pd.read_sql(
    query,
    connection,
    params=(
        "Class XII A",
    )
)

41. JOIN vs Multiple Queries

Suppose an application needs every student's class name.

A poorly designed approach might retrieve students first and then query the class separately for every student.

Get Student 1
    ↓
Get Class 1

Get Student 2
    ↓
Get Class 2

Get Student 3
    ↓
Get Class 3

A single JOIN can often retrieve the required information more efficiently.

SELECT
    s.name,
    c.class_name
FROM students AS s
JOIN classes AS c
    ON s.class_id = c.id;

42. The N+1 Query Problem

The pattern of performing one query to retrieve a collection and then another query for each item is commonly known as the N+1 query problem.

Excessive database round trips can hurt application performance. Appropriate joins, batching, eager loading, or other data-access techniques can reduce unnecessary queries.

43. Database Normalization

Normalization is a database design approach that organizes data to reduce unnecessary duplication and improve integrity.

A simplified example:

Before:

Student
Name
Class
Class Teacher
Class Room


After:

Students
---------
id
name
class_id


Classes
---------
id
class_name
teacher_id
room_id

44. Why Normalization Matters

• Reduces unnecessary data duplication.
• Improves consistency.
• Makes updates easier.
• Creates clearer relationships.
• Supports data integrity.
• Provides a structured foundation for queries.

45. Denormalization

Denormalization intentionally introduces some redundancy for specific performance or reporting requirements.

It should be a deliberate design decision rather than an accidental result of poor database design.

46. Referential Integrity

Referential integrity ensures that relationships between related tables remain valid.

students.class_id
        |
        v
classes.id

A foreign-key constraint can prevent a student from referencing a class that does not exist.

47. Foreign Key Actions

Foreign keys can be configured with actions controlling what happens when referenced records are updated or deleted.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150),
    class_id INT,

    FOREIGN KEY (class_id)
        REFERENCES classes(id)
        ON DELETE SET NULL
);

Other actions include options such as CASCADE and RESTRICT, depending on the database design and desired behavior.

48. CASCADE Example

CREATE TABLE student_subjects (
    student_id INT NOT NULL,
    subject_id INT NOT NULL,

    PRIMARY KEY (
        student_id,
        subject_id
    ),

    FOREIGN KEY (student_id)
        REFERENCES students(id)
        ON DELETE CASCADE,

    FOREIGN KEY (subject_id)
        REFERENCES subjects(id)
        ON DELETE CASCADE
);

When appropriate, cascading can automatically remove dependent relationship records when their parent record is deleted.

49. Composite Primary Key

A composite primary key consists of more than one column.

CREATE TABLE student_subjects (
    student_id INT NOT NULL,
    subject_id INT NOT NULL,

    PRIMARY KEY (
        student_id,
        subject_id
    )
);

The combination of student ID and subject ID must be unique.

50. Practical School Database Model

Students
    |
    | class_id
    v
Classes


Students
    |
    | student_id
    v
Student_Subjects
    ^
    | subject_id
    |
Subjects


Students
    |
    v
Marks
    |
    +---- Subjects
    |
    +---- Exams

This model can form the basis of a school academic-management database.

51. Example Marks Table

CREATE TABLE marks (
    id INT AUTO_INCREMENT PRIMARY KEY,

    student_id INT NOT NULL,

    subject_id INT NOT NULL,

    exam_id INT NOT NULL,

    marks DECIMAL(5, 2),

    FOREIGN KEY (student_id)
        REFERENCES students(id),

    FOREIGN KEY (subject_id)
        REFERENCES subjects(id),

    FOREIGN KEY (exam_id)
        REFERENCES exams(id)
);

52. Retrieve Student Marks

SELECT
    s.name AS student_name,
    sub.subject_name,
    e.exam_name,
    m.marks
FROM marks AS m

INNER JOIN students AS s
    ON m.student_id = s.id

INNER JOIN subjects AS sub
    ON m.subject_id = sub.id

INNER JOIN exams AS e
    ON m.exam_id = e.id

ORDER BY
    s.name,
    sub.subject_name;

53. Calculate Student Average

SELECT
    s.id,
    s.name,
    AVG(m.marks) AS average_marks
FROM students AS s

INNER JOIN marks AS m
    ON s.id = m.student_id

GROUP BY
    s.id,
    s.name

ORDER BY
    average_marks DESC;

This type of query is useful for academic analytics and reporting.

54. Find Students Above a Threshold

SELECT
    s.name,
    AVG(m.marks) AS average_marks
FROM students AS s

INNER JOIN marks AS m
    ON s.id = m.student_id

GROUP BY
    s.id,
    s.name

HAVING AVG(m.marks) >= 80

ORDER BY
    average_marks DESC;

55. Python Function for Joined Student Data

def get_student_class_data(
    connection
):

    cursor = connection.cursor(
        dictionary=True
    )

    try:

        cursor.execute(
            """
            SELECT
                s.id AS student_id,
                s.name AS student_name,
                c.class_name
            FROM students AS s
            INNER JOIN classes AS c
                ON s.class_id = c.id
            ORDER BY s.name
            """
        )

        return cursor.fetchall()

    finally:

        cursor.close()

56. Returning JOIN Results as DataFrames

def student_class_dataframe(
    connection
):

    query = """
    SELECT
        s.id AS student_id,
        s.name AS student_name,
        c.class_name
    FROM students AS s
    INNER JOIN classes AS c
        ON s.class_id = c.id
    ORDER BY s.name
    """

    return pd.read_sql(
        query,
        connection
    )

57. Common JOIN Mistakes

58. JOIN Quick Reference

59. Database Design Checklist

• ☐ Identify major entities.
• ☐ Create a table for each appropriate entity.
• ☐ Define primary keys.
• ☐ Identify relationships.
• ☐ Add foreign keys.
• ☐ Choose appropriate data types.
• ☐ Add required constraints.
• ☐ Avoid unnecessary duplication.
• ☐ Consider indexes for frequently queried columns.
• ☐ Test joins with realistic data.

60. Practical Examination Tasks

61. Interview Questions

62. Examination MCQs

63. Coding Challenge — School Database

classes
students
subjects
student_subjects
exams
marks

64. Self-Assessment Checklist

You should now be able to:

• ☐ Design related database tables.
• ☐ Define primary keys.
• ☐ Define foreign keys.
• ☐ Explain one-to-one relationships.
• ☐ Explain one-to-many relationships.
• ☐ Explain many-to-many relationships.
• ☐ Create junction tables.
• ☐ Use INNER JOIN.
• ☐ Use LEFT JOIN.
• ☐ Understand RIGHT JOIN.
• ☐ Use multiple joins.
• ☐ Combine JOIN with GROUP BY.
• ☐ Handle NULL values.
• ☐ Use aliases.
• ☐ Execute joins from Python.
• ☐ Load joined results into pandas.
• ☐ Recognize the N+1 query problem.
• ☐ Design a normalized relational model.

65. Key Takeaway