frameworkEngineering & architecture

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)

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]

Relations, keys, and constraints Keys connect tables while constraints preserve valid states
Relational database
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

  1. Model important entities and relationships as relations with named attributes.
  2. Define tables, data types, primary keys, foreign keys, uniqueness, and other constraints.
  3. Normalize repeated facts where this improves integrity, then denormalize selectively for measured needs.
  4. Use SQL operations to filter, join, aggregate, insert, update, and delete data.
  5. Use transactions and isolation to coordinate related changes under concurrent access.
  6. Add indexes and maintain statistics based on actual query and write patterns.

05A practical example

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.

07Sources

  1. ConceptsPostgreSQL Global Development Group · Official technical documentationConcepts
  2. The SQL LanguagePostgreSQL Global Development Group · Official technical documentationThe SQL Language
View saved topics

Start typing to search titles, definitions, categories, and frameworks.