What Is a Relational Database?
A relational database organises data into tables, where each table represents one type of entity such as customers or orders. Rows are individual records and columns are the attributes of each record. Tables are linked through primary keys and foreign keys, allowing you to combine data from multiple tables in a single query. SQL (Structured Query Language) is the standard language for reading and writing data in relational databases. Examples include PostgreSQL, MySQL, and Microsoft SQL Server.
A relational database stores data in tables with rows and columns, linked together through relationships using keys.
Tables, rows, and columns
Think of a relational database as a collection of spreadsheets, each dedicated to one type of data. A table for customers has one row per customer and columns for attributes like name, email, and registration date. A table for orders has one row per order, with columns for the order date, total amount, and a reference back to the customer.
The key difference from a spreadsheet is that the database enforces rules about the data. A column defined as an integer cannot store text. A column marked as “not null” cannot be left empty. These constraints prevent dirty data from entering the system in the first place.
Primary keys and foreign keys
Two concepts make relational databases powerful: primary keys and foreign keys.
A primary key is a unique identifier for each row. Every customers row gets a unique customer_id. No two customers share the same ID, and the database refuses to allow a duplicate.
A foreign key is a reference from one table to the primary key of another. The orders table stores the customer_id of the customer who placed the order. This creates a formal relationship between the two tables. The database can enforce that you cannot create an order referencing a customer that does not exist.
This structure lets you ask questions across tables: “Give me all orders placed by customers in Amsterdam in the last 30 days” is a single SQL query that joins the customers and orders tables.
SQL: the language of relational databases
SQL (Structured Query Language) is how you communicate with a relational database. The four most common operations are:
- SELECT: read data from one or more tables
- INSERT: add a new row
- UPDATE: change an existing row
- DELETE: remove a row
Joins extend SELECT to combine data from multiple tables. A query joining customers and orders gives you a result set with columns from both, matched by the foreign key relationship.
SQL is a declarative language: you describe what data you want, and the database figures out the most efficient way to retrieve it.
Common relational database systems
PostgreSQL is the most feature-rich open-source relational database. It supports advanced data types, full-text search, JSON columns, and a large extension ecosystem. It is the database behind Supabase and many modern web applications.
MySQL is another widely used open-source database, common in PHP applications and older web stacks. MariaDB is a community-maintained fork of MySQL with some additional features.
Microsoft SQL Server is popular in enterprise environments, particularly in organisations already using the Microsoft stack. It integrates tightly with tools like Power BI and Azure.
SQLite is a lightweight, file-based relational database used in mobile apps and for local development. It does not require a separate server process.
When relational databases make sense for businesses
A relational database is appropriate whenever your data has a clear structure and relationships matter. Customer and order data, product catalogues, invoices, user accounts, and project tracking all fit naturally into relational tables. See our article on what a SaaS application is to understand how databases underpin the applications businesses use daily.
For querying and reporting, the relational model is particularly strong. Tools like Power BI can connect directly to a relational database and query it using SQL, making it straightforward to build reports from live data. See our article on what an API is for context on how applications expose database data to the outside world.
Our tip: Name your tables as singular nouns (customer, order, product) and use consistent naming for primary keys across all tables, such as always calling it id or always using table_name_id. Consistency in naming saves significant confusion when your schema grows to 20 or 30 tables.
Frequently asked questions
What is the difference between a relational and a NoSQL database?
A relational database stores data in structured tables with fixed columns and enforces relationships between tables. A NoSQL database (such as MongoDB or Firebase Firestore) stores data as flexible documents, key-value pairs, or graphs, without a fixed schema. Relational databases are better for structured data with clear relationships, while NoSQL databases suit scenarios with variable data structures or very high write throughput where a fixed schema would be a bottleneck.
What is a primary key?
A primary key is a column, or combination of columns, that uniquely identifies each row in a table. No two rows can have the same primary key value. It is used as the reference point when you link two tables together. For example, a Customers table might use a customer_id as its primary key, and an Orders table would store that same customer_id as a foreign key to indicate which customer placed each order.
Do you need to know SQL to use a relational database?
Knowing SQL gives you the most flexibility and control when working with relational databases. However, many frameworks and platforms provide an ORM (Object-Relational Mapper) or a query builder that lets you interact with the database without writing raw SQL. Tools like Supabase also provide a visual table editor and auto-generated APIs. That said, understanding the basics of SQL, especially SELECT, INSERT, UPDATE, and simple JOIN queries, is valuable for anyone working with application data regularly.