Skip to main content

Database Design

Database design is the process of producing a detailed data model of a database to support the requirements of an information system.

Database Types​

Relational Databases​

  • Structured data
  • ACID compliance
  • SQL querying
  • Examples: MySQL, PostgreSQL, Oracle

NoSQL Databases​

  • Flexible schema
  • Horizontal scaling
  • High performance
  • Examples: MongoDB, Cassandra, DynamoDB

NewSQL Databases​

  • ACID + Scalability
  • Distributed architecture
  • Modern features
  • Examples: CockroachDB, Google Spanner

Design Principles​

Normalization​

  • Eliminate redundancy
  • Reduce anomalies
  • Improve integrity
  • Levels: 1NF to 5NF

Denormalization​

  • Improve performance
  • Reduce joins
  • Increase redundancy
  • Trade-off with normalization

Indexing​

  • Speed up queries
  • Types: B-tree, Hash, Bitmap
  • Consider write performance
  • Composite indexes

Scaling Strategies​

Replication​

  • Master-Slave
  • Master-Master
  • Read replicas
  • Consistency models

Sharding​

  • Horizontal partitioning
  • Range-based
  • Hash-based
  • Directory-based

Partitioning​

  • Vertical partitioning
  • Horizontal partitioning
  • Range partitioning
  • List partitioning

Implementation Examples​

MySQL Table Design​

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
);

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id)
);

MongoDB Schema​

// User Schema
{
  _id: ObjectId,
  username: String,
  email: String,
  created_at: Date,
  posts: [{
    title: String,
    content: String,
    created_at: Date
  }]
}

Best Practices​

  1. Schema Design

    • Understand requirements
    • Choose appropriate types
    • Consider relationships
    • Plan for growth

  2. Performance Optimization

    • Proper indexing
    • Query optimization
    • Connection pooling
    • Caching strategies

  3. Security

    • Access control
    • Encryption
    • Backup strategy
    • Audit logging

Common Challenges​

  • Data consistency
  • Performance tuning
  • Schema evolution
  • Migration strategies

Further Reading​