Full-Stack Project Development

Chapter 2: Database Design and Setup

2.1 Database Schema Design

Well-designed database schemas are crucial for application performance and data integrity.

Design Principles:

• Normalize to reduce redundancy (3NF typically)
• Use appropriate data types
• Add indexes for frequently queried columns
• Plan for scalability
• Consider relationships and foreign keys

-- Example: E-commerce schema
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_category (category_id),
    INDEX idx_name (name)
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user (user_id),
    INDEX idx_status (status)
);

2.2 Database Migrations

// Using Knex.js for migrations
exports.up = function(knex) {
    return knex.schema.createTable('users', (table) => {
        table.increments('id');
        table.string('email').unique().notNullable();
        table.string('password_hash').notNullable();
        table.timestamps(true, true);
    });
};

exports.down = function(knex) {
    return knex.schema.dropTable('users');
};

2.3 Database Seeding

// Seed initial data
exports.seed = async function(knex) {
    await knex('users').del();
    await knex('users').insert([
        { email: 'admin@example.com', password_hash: 'hashed' },
        { email: 'user@example.com', password_hash: 'hashed' }
    ]);
};