Lecture on Databases in System Design

Importance of Database Choice

• Impact on Design: The scalability and effectiveness of system design are deeply influenced by database choice.
• Functional vs. Non-Functional Requirements:
  • Functional requirements can be satisfied by any database.
  • Non-functional requirements (NFRs) such as query patterns, data structure, and scale are impacted by the database choice.

Factors Influencing Database Choice

1. Structure of Data: Whether data is structured or unstructured.
2. Query Pattern: The type and frequency of queries.
3. Scale: The amount of data and scale required to handle.

Common Use Cases and Database Solutions

Caching Solutions

• Purpose: Reduce database queries and latency in remote calls.
• Key-Value Stores: Used for caching; examples include Redis, Memcached, etc.
• Recommendation: Redis is preferred for its stability and wide use.

File Storage Options

• Blob Storage: For storing images/videos; not traditional databases.
• Example: Amazon S3, often combined with a Content Delivery Network (CDN) for distribution.

Text Search Capabilities

• Text Search Engines: Elastic Search and Solr, built on Apache Lucene.
• Fuzzy Search: Supports minor errors in search terms (edit distance).
• Note: These are not databases but provide advanced searching capabilities.

Metric Storage

• Time Series Databases: Optimized for sequential data input and time-range queries.
• Examples: InfluxDB, openTSDB.

Analytical Data Storage

• Data Warehouses: For company-wide analytics, not for transactional systems.
• Example: Hadoop is used for offline reporting and analytics.

SQL vs. NoSQL

Structured vs. Unstructured Data

• Structured Data: Relational databases (RDBMS) are suitable. Examples: MySQL, Oracle.
• Unstructured Data: NoSQL databases are more flexible.

Relational Databases

• ACID Requirements: Necessary for systems needing atomicity and consistency (e.g., payment systems).
• Examples: MySQL, Oracle, SQL Server, Postgres.

Document Databases

• For systems with varying data attributes and querying needs.
• Examples: MongoDB, Couchbase.

Columnar Databases

• Use Case: Ever-increasing data with specific query types (e.g., Uber driver locations).
• Examples: Cassandra, HBase.

Real-World Scenarios

Hybrid Database Solutions

• Example: E-commerce platforms like Amazon may use a mix of RDBMS for inventory and columnar databases for order history.
• Combination Strategy: Use multiple databases to leverage strengths and meet both functional and non-functional needs.

Recommendations

• Use the framework to quickly decide on databases in interviews.
• For real-world applications, deeper exploration of options is advised.
• There are numerous database alternatives beyond those discussed; choose based on specific use-case needs.