Relational Database
A database model that represents data as relations, commonly exposed as tables whose keys and constraints express structure and integrity.
Download Markdown (.md)Soroush’s Product Wiki · https://wiki.soroush.uk/topic/relational-database/
01What it means
A relational database organizes data according to the relational model. In common systems, relations appear as tables containing rows and typed columns; keys identify rows and connect related data, while constraints preserve valid states. SQL is the prevalent language for defining and working with relational data, but it is distinct from the model itself.[1][2]
- Tables
- Relations with rows and columns
- Primary keys
- Identify rows
- Foreign keys
- Connect related facts
- Constraints
- Preserve valid states
- Transactions
- Coordinate concurrent changes
- Indexes
- Support measured access paths
Tables are a common representation of the relational model; SQL is the prevalent language, not the model itself.
02Why it matters
Relational databases provide expressive queries, explicit structure, integrity constraints, and transactional coordination for connected product data. They are a strong general-purpose choice when correctness and flexible combinations of well-understood entities matter.
03When to use it
Use a relational database for structured data with meaningful relationships, integrity rules, transactions, and evolving query needs. Another model may fit better when the primary workload is a simple key lookup, a large graph traversal, a flexible aggregate document, or a specialized analytical stream.
04How it works
- Model important entities and relationships as relations with named attributes.
- Define tables, data types, primary keys, foreign keys, uniqueness, and other constraints.
- Normalize repeated facts where this improves integrity, then denormalize selectively for measured needs.
- Use SQL operations to filter, join, aggregate, insert, update, and delete data.
- Use transactions and isolation to coordinate related changes under concurrent access.
- Add indexes and maintain statistics based on actual query and write patterns.
05A practical example
A subscription product keeps accounts, plans, subscriptions, and invoices in related tables. Foreign keys reject invoices for unknown subscriptions, a transaction changes the plan and records its billing event together, and joins support support-team and finance queries.
06Where it breaks
Relational structure does not automatically produce a good model or efficient queries. Joins, constraints, migrations, and distributed scaling require care; rigidly normalizing every access path can hurt performance, while careless denormalization can reintroduce inconsistency.
Common mistakes
- Treating relational as synonymous with one vendor or with SQL syntax alone.
- Copying object structures directly into tables without modeling data relationships and invariants.
- Removing constraints to make writes easier and pushing all integrity responsibility into application code.
- Assuming relational databases cannot store flexible data or scale beyond one machine.