Models, abstraction, and databases

Edgar Codd published "A Relational Model of Data for Large Shared Data Banks" in 1970. His insight was that data should be represented as relations with tuples and attributes, and that users should specify what data they wanted rather than navigating machine storage paths. SQL emerged from IBM research in the 1970s, became an ANSI standard in 1986, and became an ISO standard in 1987. The current ISO/IEC 9075 family is SQL:2023.

ACID properties define the guarantees a relational database provides for transactions. Atomicity means a transaction either completes fully or not at all: there is no partial update state. Consistency means the database moves from one valid state to another valid state, enforcing all defined constraints. Isolation means concurrent transactions do not see each other's in-progress changes (at the configured isolation level). Durability means committed transactions survive system crashes: the data is written to persistent storage before the commit is acknowledged.

These guarantees matter because real business operations involve multiple related changes. A bank transfer must debit one account and credit another atomically. If the system crashes after the debit but before the credit, the atomicity guarantee means the debit is rolled back. Without ACID, financial systems, inventory management, and booking systems would produce incorrect results during failures or concurrent access.

SQL query optimisation uses an execution plan: a tree of operations (scans, joins, sorts, aggregations) that the query planner selects to minimise cost. Indexes on frequently queried columns allow range scans instead of full table scans. EXPLAIN ANALYSE in PostgreSQL reveals the actual execution plan and row counts, which is the first tool to reach for when a query is slow.

Eric Brewer's CAP conjecture (2000), formally proved by Seth Gilbert and Nancy Lynch in 2002, is often reduced too far. The useful form is this: when a network partition occurs, a distributed data system must choose between consistency and availability for the affected operation. Consistency means a read receives the most recent write or an error. Availability means every request receives a non-error response, without guaranteeing it is the newest value. Partition tolerance means the system continues to have defined behaviour when messages between nodes are delayed or dropped.

In practice, network partitions are not optional in a distributed system. The real trade-off is between consistency and availability during the partition. A CP design refuses reads or writes it cannot prove are consistent. An AP design serves potentially stale reads or accepts writes that will be reconciled later. Outside a partition, many systems can provide both useful availability and strong consistency for normal operations.

The right choice depends on the application. A banking ledger requires CP: serving a stale balance could allow overdrafts. A product catalogue or social media feed tolerates AP: a slightly stale product count or a delayed post count is acceptable. Most modern distributed databases (Cassandra, DynamoDB, CockroachDB, MongoDB) implement tunable consistency, allowing the application to choose the consistency level per query.

Four primary NoSQL data models address different access patterns that the relational model handles poorly or inefficiently.

• Document stores (MongoDB, Firestore) store JSON or BSON documents. Ideal for data with variable schema (product catalogues with different attributes per category), hierarchical data that would require many joins (a blog post with embedded comments), or rapid development where schema evolves frequently.
• Key-value stores (Redis, DynamoDB) retrieve values by a primary key with microsecond latency. Ideal for caching, session storage, leaderboards, and rate limiting where the access pattern is always get/set by a known key.
• Column-family stores (Apache Cassandra, HBase) store data in columns grouped into column families, optimised for wide-row writes and time-series data. Ideal for IoT sensor ingestion, event logs, and metrics where data is appended chronologically and queried by time range.
• Graph databases (Neo4j, Amazon Neptune) store nodes and edges with arbitrary properties, optimised for multi-hop traversal. Ideal for social networks, recommendation engines, fraud detection (unusual transaction graph patterns), and knowledge graphs where relationship traversal is the primary operation.

The prerequisite skill is abstraction discipline. A conceptual model names the business things and relationships. A logical model turns those into entities, attributes, constraints, and identifiers. A physical model commits to indexes, partitions, denormalisation, clustering keys, and storage engines. Senior leaders need this chain because platform choices create lock-in and operational risk. Engineers need it because a database can only be excellent for the queries it was designed to answer.