CodeToLive

SQL Database Design (DDL)

Data Definition Language (DDL) is used to define and manage database structures. Proper database design is crucial for performance, scalability, and data integrity.

Database Design Process

1. Requirements Analysis: Understand data needs
2. Conceptual Design: Entity-Relationship modeling
3. Logical Design: Convert to relational schema
4. Physical Design: Implement with DDL
5. Implementation: Create database objects

Basic DDL Commands

-- Create database
CREATE DATABASE ecommerce;

-- Create table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Alter table
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

-- Drop table
DROP TABLE customers_backup;

-- Truncate table (remove all data)
TRUNCATE TABLE temp_data;
        

Normalization

Process of organizing data to minimize redundancy:

Data Types

Constraints

-- Primary key
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL
);

-- Foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Unique constraint
ALTER TABLE products ADD CONSTRAINT uq_product_name UNIQUE (product_name);

-- Check constraint
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10,2) CHECK (salary > 0)
);

-- Default value
ALTER TABLE orders ADD COLUMN order_date DATE DEFAULT CURRENT_DATE;
        

Indexes

-- Create index
CREATE INDEX idx_customer_name ON customers(last_name, first_name);

-- Unique index
CREATE UNIQUE INDEX idx_product_sku ON products(sku);

-- Composite index
CREATE INDEX idx_order_date_customer ON orders(order_date, customer_id);

-- Drop index
DROP INDEX idx_customer_name ON customers;
        

Views

-- Create view
CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE last_order_date > DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH);

-- Materialized view (PostgreSQL)
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Update materialized view
REFRESH MATERIALIZED VIEW monthly_sales;
        

Stored Procedures & Functions

-- Stored procedure
DELIMITER //
CREATE PROCEDURE update_product_price(
    IN product_id INT,
    IN price_change DECIMAL(10,2)
BEGIN
    UPDATE products 
    SET price = price + price_change
    WHERE product_id = product_id;
END //
DELIMITER ;

-- Function
CREATE FUNCTION calculate_discount(price DECIMAL(10,2), quantity INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE discount DECIMAL(10,2);
    IF quantity > 10 THEN
        SET discount = price * 0.15;
    ELSEIF quantity > 5 THEN
        SET discount = price * 0.10;
    ELSE
        SET discount = 0;
    END IF;
    RETURN discount;
END;
        

Triggers

-- Audit trail trigger
CREATE TRIGGER log_product_changes
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit (
        product_id, 
        changed_by, 
        change_date,
        old_price,
        new_price
    ) VALUES (
        NEW.product_id,
        CURRENT_USER(),
        NOW(),
        OLD.price,
        NEW.price
    );
END;

-- Prevent deletion trigger
CREATE TRIGGER prevent_customer_deletion
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM orders WHERE customer_id = OLD.customer_id) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Cannot delete customer with existing orders';
    END IF;
END;
        

Database Design Patterns

Star Schema

-- Fact table
CREATE TABLE sales_fact (
    sale_id INT PRIMARY KEY,
    date_id INT,
    product_id INT,
    customer_id INT,
    quantity INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (date_id) REFERENCES date_dim(date_id),
    FOREIGN KEY (product_id) REFERENCES product_dim(product_id),
    FOREIGN KEY (customer_id) REFERENCES customer_dim(customer_id)
);

-- Dimension tables
CREATE TABLE product_dim (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    supplier VARCHAR(100)
);

CREATE TABLE customer_dim (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    region VARCHAR(50),
    segment VARCHAR(50)
);
        

Many-to-Many Relationship

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
        

Performance Considerations

• Choose appropriate data types (smallest that fits)
• Normalize for integrity, denormalize for performance
• Consider partitioning for large tables
• Use constraints for data integrity
• Document your schema design