Module 3: Multi-Table Queries

1. Foreign Key Relationships

Understanding the fundamentals of table relationships:

• Primary and foreign keys
• Referential integrity
• Relationship types
• Cascade operations

2. Multi-Table Queries

Master the art of querying across multiple tables:

• JOIN operations
• Subqueries
• Aggregate functions
• Query optimization

3. Database Access Methods

Learn to write efficient database access methods:

• Creating reusable queries
• Error handling
• Query performance
• Best practices

Foreign Keys in Database Design

Foreign keys are a fundamental concept in relational databases that establish connections between tables, enforcing referential integrity and defining relationships.

Role and Importance of Foreign Keys

• Relationship Definition: Foreign keys formally establish connections between related data across tables.
• Referential Integrity: Prevent orphaned records by ensuring that referenced data exists.
• Cascading Actions: Configure automatic behaviors when referenced records are updated or deleted.
• Data Organization: Enable normalization by allowing data to be split across multiple tables.

Example of creating foreign key relationships:

-- Creating tables with foreign key relationships
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  department_id INTEGER,
  FOREIGN KEY (department_id) REFERENCES departments(id)
    ON DELETE SET NULL  -- If department is deleted, set to NULL
    ON UPDATE CASCADE   -- If department ID changes, update automatically
);
                

With Knex.js, you can create foreign key relationships in migrations:

exports.up = function(knex) {
  return knex.schema
    .createTable('departments', table => {
      table.increments('id');
      table.string('name').notNullable().unique();
    })
    .createTable('employees', table => {
      table.increments('id');
      table.string('first_name').notNullable();
      table.string('last_name').notNullable();
      table.integer('department_id')
        .references('id')
        .inTable('departments')
        .onDelete('SET NULL')
        .onUpdate('CASCADE');
    });
};
                

Multi-Table Queries

Querying data from multiple tables is a core capability of relational databases, allowing you to combine and analyze related information across your database.

JOIN Operations

• INNER JOIN: Returns records with matching values in both tables.
• LEFT JOIN: Returns all records from the left table and matching records from the right table.
• RIGHT JOIN: Returns all records from the right table and matching records from the left table.
• FULL JOIN: Returns all records when there's a match in either table.

Example SQL JOIN queries:

-- INNER JOIN: Get employees with their department names
SELECT 
  employees.id, 
  employees.first_name,
  employees.last_name,
  departments.name AS department_name
FROM 
  employees
INNER JOIN 
  departments ON employees.department_id = departments.id;

-- LEFT JOIN: Get all employees, even those without departments
SELECT 
  employees.id, 
  employees.first_name,
  employees.last_name,
  departments.name AS department_name
FROM 
  employees
LEFT JOIN 
  departments ON employees.department_id = departments.id;
                

Using Knex.js for joins:

// INNER JOIN with Knex
knex('employees')
  .select(
    'employees.id',
    'employees.first_name',
    'employees.last_name',
    'departments.name as department_name'
  )
  .join('departments', 'employees.department_id', 'departments.id')
  .then(employees => {
    console.log(employees);
  });

// LEFT JOIN with Knex
knex('employees')
  .select(
    'employees.id',
    'employees.first_name',
    'employees.last_name',
    'departments.name as department_name'
  )
  .leftJoin('departments', 'employees.department_id', 'departments.id')
  .then(employees => {
    console.log(employees);
  });
                

Database Access Methods

Well-designed database access methods encapsulate your database interactions, making your code more maintainable, testable, and reusable.

Best Practices for Database Access

• Modularization: Organize database operations by entity or domain.
• Abstraction: Hide query implementation details behind a clean API.
• Error Handling: Implement consistent error management for database operations.
• Transaction Support: Group related operations to ensure data consistency.

Example of database access methods with Knex.js:

// employees-model.js
const db = require('../data/db-config');

module.exports = {
  findAll,
  findById,
  findByDepartment,
  create,
  update,
  remove
};

function findAll() {
  return db('employees')
    .select(
      'employees.id',
      'employees.first_name',
      'employees.last_name',
      'departments.name as department_name'
    )
    .leftJoin('departments', 'employees.department_id', 'departments.id');
}

function findById(id) {
  return db('employees')
    .select(
      'employees.id',
      'employees.first_name',
      'employees.last_name',
      'departments.name as department_name'
    )
    .leftJoin('departments', 'employees.department_id', 'departments.id')
    .where('employees.id', id)
    .first();
}

function findByDepartment(departmentId) {
  return db('employees')
    .where('department_id', departmentId);
}

async function create(employee) {
  const [id] = await db('employees').insert(employee);
  return findById(id);
}

function update(id, changes) {
  return db('employees')
    .where('id', id)
    .update(changes)
    .then(() => findById(id));
}

function remove(id) {
  return db('employees')
    .where('id', id)
    .del();
}