Manu's Vault

Relational Databases

4 min readStatus:Seedling

Core Idea

Relational databases organize data into tables (relations) of rows and columns based on E.F.

Definition

Relational databases organize data into tables (relations) of rows and columns based on E.F. Codd’s 1970 relational model. Each row represents a record with a unique identifier (primary key), columns represent attributes, and tables connect through foreign keys to enable complex queries across relationships. The relational model separates logical data structures from physical storage, letting administrators manage storage independently of how applications access data. Combined with SQL as a standardized query language, this made relational databases the dominant paradigm for structured data storage over five decades.

Key Characteristics

  • Tabular structure: Data organized into tables with rows (records) and columns (attributes)

    • Each row represents a distinct entity instance with unique identification
    • Tables represent entity types (customers, products, orders) in normalized form

  • Relationships via keys: Tables connect through primary and foreign key constraints

    • Primary keys uniquely identify each row (e.g., customer_id)
    • Foreign keys reference primary keys in other tables
    • Referential integrity ensures foreign keys always point to valid records

  • ACID compliance: Guarantees reliable transactions through Atomicity, Consistency, Isolation, Durability

    • Transactions complete fully or fail fully (atomicity)
    • Database maintains validity rules (consistency)
    • Concurrent transactions don’t interfere (isolation)
    • Committed changes survive failures (durability)
    • See ACID for detailed properties

  • SQL interface: Standardized declarative language for data manipulation

    • ANSI standard since 1986, adopted across all major RDBMS platforms
    • Supports joins, aggregations, subqueries, set operations across tables

  • Normalization: Design process eliminating redundancy and improving integrity

    • Normal forms (1NF, 2NF, 3NF) progressively reduce duplication
    • Each fact stored once, changes propagate consistently
    • Trade-off: normalization reduces redundancy, denormalization improves read performance

  • Schema enforcement: Fixed table structures with predefined types and constraints

    • Data validates against schema before insertion
    • Strong typing prevents invalid data entry

Why It Matters

Relational databases remain the default for applications requiring strong consistency, data integrity, and complex querying across structured data. Financial systems, e-commerce, inventory management, and enterprise applications depend on ACID properties for data correctness. Five decades of development provides robust tooling, extensive community knowledge, and battle-tested implementations.

However, the CAP Theorem proves distributed systems cannot simultaneously guarantee consistency, availability, and partition tolerance. Relational databases prioritize consistency and partition tolerance, potentially reducing availability during network failures. This led to NoSQL alternatives (key-value, document, wide-column, graph) favoring availability and horizontal scalability through eventual consistency.

Modern architectures must choose database types based on data characteristics and consistency needs. Bounded contexts often benefit from different databases—relational for transactional data requiring strong consistency, NoSQL for high-volume reads with relaxed consistency.

Examples

  • PostgreSQL and MySQL: Open-source RDBMS platforms powering millions of applications

    • PostgreSQL emphasizes SQL standards and extensibility with custom data types
    • MySQL optimized for read-heavy workloads with replication

  • Banking transactions: Money transfers require atomicity and durability

    • Guarantee account balances remain consistent after every transaction
    • Transaction logs enable audit trails and regulatory compliance

  • E-commerce inventory: Order processing joins customers, products, inventory, shipping tables

    • Single query retrieves all related data across relationships
    • Foreign keys prevent orders for non-existent products
    • Transaction isolation prevents overselling during concurrent orders

  • Enterprise RDBMS: Oracle and SQL Server provide advanced features

    • Global distribution with strong consistency (Oracle RAC, SQL Server Always On)
    • Sophisticated optimization for complex analytical workloads

Sources

Note

This content was drafted with assistance from AI tools for research, organization, and initial content generation. All final content has been reviewed, fact-checked, and edited by the author to ensure accuracy and alignment with the author’s intentions and perspective.

Graph View

Backlinks