Module 4: Data Modeling

1. Data Normalization

Understanding database normalization principles:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Denormalization considerations

2. Table Relationships

Exploring different types of table relationships:

• One-to-One relationships
• One-to-Many relationships
• Many-to-Many relationships
• Self-referential relationships

3. Knex Table Relationships

Implementing relationships using Knex:

• Setting up foreign keys
• Creating migrations
• Managing relationships
• Best practices

Data Normalization Principles

Data normalization is the process of organizing a database to reduce redundancy and improve data integrity through a series of rules called normal forms.

The Three Main Normal Forms

• First Normal Form (1NF):
  • Each table has a primary key
  • Each column contains atomic (indivisible) values
  • No repeating groups of columns
• Second Normal Form (2NF):
  • Must be in 1NF
  • All non-key attributes fully depend on the primary key
  • No partial dependencies (relevant for composite keys)
• Third Normal Form (3NF):
  • Must be in 2NF
  • No transitive dependencies (non-key columns depend only on the primary key)
  • Eliminates fields that do not depend directly on the primary key

Example of normalizing data:

-- Unnormalized data (violations of normal forms)
CREATE TABLE orders_unnormalized (
  order_id INTEGER,
  customer_name TEXT,
  customer_email TEXT,
  customer_address TEXT,
  product_id INTEGER,
  product_name TEXT,
  product_category TEXT,
  product_price REAL,
  quantity INTEGER,
  order_date DATE
);

-- Normalized to 3NF (multiple related tables)
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  address TEXT
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT,
  price REAL NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);
                

Database Relationship Types

Understanding different types of table relationships is crucial for effective data modeling. Each relationship type serves specific purposes in representing real-world connections between entities.

Primary Relationship Types

• One-to-One (1:1):
  • Each record in Table A relates to exactly one record in Table B and vice versa
  • Example: User and User Profile
  • Implementation: Foreign key with unique constraint
• One-to-Many (1:N):
  • Each record in Table A can relate to multiple records in Table B, but each record in Table B relates to only one record in Table A
  • Example: Department and Employees
  • Implementation: Foreign key in the "many" table
• Many-to-Many (N:M):
  • Each record in Table A can relate to multiple records in Table B, and vice versa
  • Example: Students and Courses
  • Implementation: Junction table with foreign keys to both tables

Examples of relationship implementations:

-- One-to-One: User and User Profile
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
  user_id INTEGER PRIMARY KEY,
  bio TEXT,
  avatar_url TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- One-to-Many: Department and Employees
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Many-to-Many: Students and Courses
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  enrollment_date DATE NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);
                

Creating Table Relationships with Knex

Knex.js provides a powerful and expressive API for creating and managing table relationships in your migrations.

Implementing Different Relationship Types

• Foreign Key Methods:
  • table.foreign() - Creates a foreign key constraint
  • table.references() - Specifies the referenced column
  • table.inTable() - Specifies the referenced table
• Constraint Actions:
  • onDelete() - Action when referenced row is deleted
  • onUpdate() - Action when referenced key is updated

Example of creating relationships with Knex.js:

// One-to-One relationship: User and Profile
exports.up = function(knex) {
  return knex.schema
    .createTable('users', table => {
      table.increments('id');
      table.string('username').notNullable().unique();
      table.string('email').notNullable().unique();
    })
    .createTable('profiles', table => {
      // Primary key is also a foreign key
      table.integer('user_id').primary();
      table.text('bio');
      table.string('avatar_url');
      
      // Define foreign key relationship
      table.foreign('user_id')
        .references('id')
        .inTable('users')
        .onDelete('CASCADE');
    });
};

// One-to-Many relationship: Departments and Employees
exports.up = function(knex) {
  return knex.schema
    .createTable('departments', table => {
      table.increments('id');
      table.string('name').notNullable();
    })
    .createTable('employees', table => {
      table.increments('id');
      table.string('name').notNullable();
      
      // Foreign key in the "many" table
      table.integer('department_id')
        .references('id')
        .inTable('departments')
        .onDelete('SET NULL');
    });
};

// Many-to-Many relationship: Students and Courses
exports.up = function(knex) {
  return knex.schema
    .createTable('students', table => {
      table.increments('id');
      table.string('name').notNullable();
    })
    .createTable('courses', table => {
      table.increments('id');
      table.string('title').notNullable();
    })
    .createTable('enrollments', table => {
      // Composite primary key
      table.integer('student_id')
        .notNullable()
        .references('id')
        .inTable('students')
        .onDelete('CASCADE');
        
      table.integer('course_id')
        .notNullable()
        .references('id')
        .inTable('courses')
        .onDelete('CASCADE');
        
      table.date('enrollment_date').notNullable();
      
      // Define the composite primary key
      table.primary(['student_id', 'course_id']);
    });
};