Creating Related Tables in SQL: A Practical Guide

Understanding Table Relationships Through Real-World Examples

Imagine you're organizing a school system. In a school, there are teachers and students. Each teacher can have many students in their class, but in our simplified example, each student has just one primary teacher. This is a perfect example of a one-to-many relationship that we can model in our database. Later, we'll look at how to handle more complex relationships, like students taking multiple classes with different teachers.

Let's start by looking at how we can create tables that reflect these real-world relationships in our database.

Creating One-to-Many Relationships

Let's begin with our school example. We'll first create a table for teachers, then create a related table for students. The key here is to understand that we'll need a way to connect each student to their teacher.

/* First, let's create our teachers table. Each teacher needs:
 * - A unique identifier (primary key)
 * - A name
 * - A subject they teach
 * - The room number where they teach
 */
CREATE TABLE teachers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    room_number VARCHAR(10),
    hire_date DATE
);

/* Now let's create our students table with a connection to teachers.
 * Each student needs:
 * - A unique identifier (primary key)
 * - Basic information like name and grade
 * - A way to connect to their teacher (foreign key)
 */
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    grade_level INTEGER CHECK (grade_level BETWEEN 1 AND 12),
    enrollment_date DATE,
    teacher_id INTEGER,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

Notice how we've created the relationship: the students table has a teacher_id column that references the id column in the teachers table. This is like giving each student a card with their teacher's ID number on it. Let's see how we could use these tables:

/* Let's add some sample data to see how it works */
INSERT INTO teachers (id, name, subject, room_number) VALUES
    (1, 'Ms. Thompson', 'Mathematics', '101A'),
    (2, 'Mr. Rodriguez', 'Science', '102B');

INSERT INTO students (id, name, grade_level, teacher_id) VALUES
    (1, 'Alice Johnson', 9, 1),
    (2, 'Bob Smith', 9, 1),
    (3, 'Carol White', 9, 2);

Creating Many-to-Many Relationships

Now let's tackle a more complex scenario: students taking multiple classes, with each class having multiple students. This is a many-to-many relationship, and it requires a special approach using what we call a join table or bridge table.

/* First, let's create a courses table 
 * (we'll keep teachers separate for this example) */
CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    credits INTEGER DEFAULT 1,
    description TEXT
);

/* Next, create a table to track student enrollments.
 * This is our join table - it connects students to courses */
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade VARCHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)  -- Prevents duplicate enrollments
);

The enrollments table is special because it exists primarily to create relationships between students and courses. Let's see it in action:

/* Add some courses */
INSERT INTO courses (id, name, subject, credits) VALUES
    (1, 'Algebra I', 'Mathematics', 3),
    (2, 'Biology', 'Science', 3),
    (3, 'World History', 'Social Studies', 3);

/* Enroll students in courses */
INSERT INTO enrollments (student_id, course_id) VALUES
    (1, 1),  -- Alice takes Algebra
    (1, 2),  -- Alice also takes Biology
    (2, 1),  -- Bob takes Algebra
    (2, 3),  -- Bob takes World History
    (3, 2);  -- Carol takes Biology

Understanding Foreign Key Constraints

Foreign key constraints are like having a vigilant administrator checking every relationship in your database. They prevent invalid connections, like assigning a student to a teacher who doesn't exist. Let's explore how they work:

/* First, enable foreign key constraints in SQLite */
PRAGMA foreign_keys = ON;

/* This will succeed because teacher 1 exists */
INSERT INTO students (id, name, grade_level, teacher_id) 
VALUES (4, 'David Brown', 9, 1);

/* This will fail because teacher 99 doesn't exist */
INSERT INTO students (id, name, grade_level, teacher_id) 
VALUES (5, 'Eva Green', 9, 99);

/* This will succeed because NULL is allowed 
 * (unless we add NOT NULL to teacher_id) */
INSERT INTO students (id, name, grade_level, teacher_id)
VALUES (6, 'Frank Wilson', 9, NULL);

Foreign key constraints also protect your data when you try to delete related records:

/* This will fail if teacher 1 has students */
DELETE FROM teachers WHERE id = 1;

/* To allow deletes, you could modify the students table like this */
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    grade_level INTEGER,
    teacher_id INTEGER,
    FOREIGN KEY (teacher_id) 
        REFERENCES teachers(id)
        ON DELETE SET NULL  -- Makes student's teacher_id NULL if teacher is deleted
);

Practical Examples: Building a Complete School System

Let's put everything together to create a more complete school management system:

/* Create departments */
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    budget DECIMAL(10,2)
);

/* Modify teachers table to include department */
CREATE TABLE teachers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    salary DECIMAL(10,2),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

/* Create a courses table with teacher assignments */
CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    teacher_id INTEGER,
    semester VARCHAR(20),
    year INTEGER,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

/* Create a more detailed enrollments table */
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade VARCHAR(2),
    attendance_percentage DECIMAL(5,2),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id, enrollment_date)
);

Now we can track more complex relationships and data:

/* Find all courses and students for a specific teacher */
SELECT 
    t.name as teacher_name,
    c.name as course_name,
    s.name as student_name,
    e.grade
FROM teachers t
JOIN courses c ON t.id = c.teacher_id
JOIN enrollments e ON c.id = e.course_id
JOIN students s ON e.student_id = s.id
WHERE t.id = 1
ORDER BY c.name, s.name;

Best Practices and Common Pitfalls

When creating related tables, keep these important principles in mind:

/* 1. Always enable foreign key constraints */
PRAGMA foreign_keys = ON;

/* 2. Consider what happens when records are deleted */
CREATE TABLE assignments (
    id INTEGER PRIMARY KEY,
    course_id INTEGER,
    title VARCHAR(100),
    due_date DATE,
    FOREIGN KEY (course_id) 
        REFERENCES courses(id)
        ON DELETE CASCADE  -- Deletes assignments when course is deleted
);

/* 3. Use appropriate data types and constraints */
CREATE TABLE grades (
    student_id INTEGER,
    assignment_id INTEGER,
    score DECIMAL(5,2) CHECK (score BETWEEN 0 AND 100),
    submission_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (assignment_id) REFERENCES assignments(id)
);

/* 4. Consider using indexes for frequently queried columns */
CREATE INDEX idx_enrollments_student 
    ON enrollments(student_id);
CREATE INDEX idx_enrollments_course 
    ON enrollments(course_id);

Conclusion

Creating related tables in SQL is about more than just connecting data - it's about modeling real-world relationships in a way that maintains data integrity while allowing for flexible queries and updates. By understanding the different types of relationships and how to implement them properly, you can create database structures that accurately reflect your application's needs.

Remember these key points:

• Always enable foreign key constraints to maintain data integrity

• Choose the appropriate relationship type (one-to-many or many-to-many) based on your real-world requirements

• Use join tables for many-to-many relationships

• Consider what should happen when related records are deleted

• Add appropriate indexes to improve query performance