Database Design

Introduction

Database design is the process of producing a detailed model of a database. This model contains the necessary logical and physical design choices and physical storage parameters needed to generate a design that meets defined requirements.

Design Objectives:

Data integrity
Data consistency
Minimal redundancy
Efficient retrieval
Data independence
Scalability

Design Methodology

Requirements Analysis

Key Steps:

Gather requirements
Identify entities
Define relationships
Determine constraints

Conceptual Design

Example of Entity-Relationship Diagram notation:

/* Entity definition example */
[Customer]
    - customer_id (PK)
    - name
    - email
    |
    |--< [Order] (1:N relationship)
        - order_id (PK)
        - customer_id (FK)
        - order_date
        |
        |--< [OrderItem] (1:N relationship)
            - item_id (PK)
            - order_id (FK)
            - product_id (FK)
            - quantity

Normalization

First Normal Form (1NF)

Example of converting to 1NF:

/* Before 1NF */
CREATE TABLE Customer (
    customer_id INT,
    name VARCHAR(100),
    phone_numbers VARCHAR(255) -- Multiple numbers as comma-separated
);

/* After 1NF */
CREATE TABLE Customer (
    customer_id INT,
    name VARCHAR(100)
);

CREATE TABLE CustomerPhone (
    customer_id INT,
    phone_number VARCHAR(20)
);

Second Normal Form (2NF)

Example of 2NF transformation:

/* Before 2NF */
CREATE TABLE OrderDetail (
    order_id INT,
    product_id INT,
    quantity INT,
    product_name VARCHAR(100), -- Dependent on product_id only
    unit_price DECIMAL(10,2)
);

/* After 2NF */
CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    unit_price DECIMAL(10,2)
);

CREATE TABLE OrderDetail (
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

Relationships

Types of Relationships

Common Relationships:

One-to-One (1:1)
One-to-Many (1:N)
Many-to-Many (M:N)

Implementation Examples

/* One-to-One: User and Profile */
CREATE TABLE User (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE Profile (
    user_id INT PRIMARY KEY,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES User(user_id)
);

/* Many-to-Many: Students and Courses */
CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

Design Patterns

Common Patterns

Database Patterns:

Identity Field
Foreign Key Mapping
Association Table Mapping
Inheritance Mapping
Embedded Value
Optimistic Locking

Implementation Example

/* Inheritance Pattern Example */
CREATE TABLE Person (
    person_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE Employee (
    person_id INT PRIMARY KEY,
    employee_id VARCHAR(20),
    department VARCHAR(50),
    FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

CREATE TABLE Customer (
    person_id INT PRIMARY KEY,
    customer_number VARCHAR(20),
    loyalty_points INT,
    FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

Best Practices

Design Guidelines

Best Practices:

Use appropriate data types
Implement proper constraints
Consider indexing strategy
Plan for scalability
Document design decisions
Follow naming conventions

Performance Considerations

Key Factors:

Query optimization
Index design
Denormalization when needed
Partitioning strategy
Caching considerations