Dr. Soumadip Biswas
Computer Science & Engineering | Student | Researcher | Educator

Relational Guarantees & Schema Design

Understanding ACID properties, the relational model, and the strict discipline of database normalization.

v1.0.0 Updated: September 11, 2026

Why Databases Exist

A naive approach to saving application state is writing a JSON object to a text file. This works perfectly until two separate web requests attempt to modify the same file at the exact same millisecond, corrupting the file permanently.

Databases exist to solve the chaotic reality of concurrent access and hardware failure. A relational database management system (RDBMS) like PostgreSQL or MySQL acts as a rigorous gatekeeper, ensuring that data transitions from one valid state to another, regardless of power outages, network drops, or competing threads.

💡
Architectural Note: Applications are rewritten, frameworks are replaced, and APIs are versioned, but the underlying data remains. The database schema is the most permanent architectural contract in your system. Design it defensively.

The ACID Guarantees

Relational databases provide four non-negotiable guarantees, collectively known as ACID:

  1. Atomicity: A transaction (which may contain multiple queries) is an all-or-nothing proposition. If an engineer attempts to deduct funds from Account A and add them to Account B, and the server crashes mid-way, the database rolls back the entire transaction. Partial states are never saved.
  2. Consistency: The database strictly enforces its schema constraints (e.g., Foreign Keys, NOT NULL, UNIQUE). A transaction cannot leave the database in an illegal state.
  3. Isolation: Concurrent transactions execute as if they are the only transaction running on the system. We will explore Isolation Levels deeply in Module 4.
  4. Durability: Once the database returns a 200 OK (COMMIT successful), the data is permanently written to non-volatile storage. If the power cord is pulled a millisecond later, the data survives.

Schema Design & Normalization

The Relational Model, invented by Edgar F. Codd in 1970, is based on mathematical set theory. The goal of schema design is Normalization: organizing data to eliminate redundancy and prevent anomalies during updates, insertions, or deletions.

  • First Normal Form (1NF): Every column must contain atomic (indivisible) values. (e.g., Do not store a comma-separated list of tags in a single string column).
  • Second Normal Form (2NF): Eliminate redundant data across multiple rows by extracting it into separate tables and linking them with Foreign Keys.
  • Third Normal Form (3NF): Ensure every non-key column is strictly dependent on the Primary Key. (e.g., Do not store a user_id, department_id, and department_name in the same table. The department_name depends on the department_id, not the user_id).
erDiagram USERS ||--o{ ORDERS : places USERS { uuid id PK string email UK string name } ORDERS ||--|{ ORDER_ITEMS : contains ORDERS { int id PK uuid user_id FK timestamp created_at } ORDER_ITEMS { int id PK int order_id FK int product_id FK int quantity }

The Foreign Key Contract

The Foreign Key is the physical manifestation of the relational model. It guarantees Referential Integrity.

If ORDER_ITEMS.product_id is a Foreign Key referencing PRODUCTS.id, the database makes it mathematically impossible to delete a product if an order still references it, preventing “orphan records” and silent data corruption.

Test Your Understanding

Q:A developer suggests removing all Foreign Key constraints from the database to speed up `INSERT` performance, arguing that the application's ORM (Object-Relational Mapper) will handle the relationship logic. Is this an acceptable architectural trade-off? Reveal â–¾
No. This is a catastrophic anti-pattern. Relying on an application layer to enforce data integrity is fundamentally flawed because applications can scale horizontally (multiple instances running concurrently) and suffer from race conditions. The ORM might verify a product exists, but a split-second later, another application instance might delete that product before the first instance inserts the order. Only the database, holding the centralized locks, can guarantee absolute referential integrity.

Further Exploration