What is a relational database?
When to use them and how Canonical services can make your relational database operations secure and efficient.
A system for managing digital information
Relational databases are systems to store, retrieve and manage digital information. They are one of many types of databases and, as their name implies, they enable us to express relations between groups of data.
Relational database management systems (RDBMS) organise data into tables. Each table is a set of rows where every cell has a type defined by its column. Each row is uniquely identified among all rows in the same table by a set of columns, called the primary key. A table can relate to another table by referencing its primary key. This relation is often called a foreign key. Foreign keys are used to express and enforce inclusion type relations between different tables at a database level.
For example, you can express that a table is the “child” of another one. The orders table can be considered a child of the customers table in the following diagram:
Relational model and relational databases
Relational databases are named after the relational model formalised by Edgar Frank Codd (also known as Ted Codd) in 1969. The diagram below maps the relational model terminology, as presented by Ted Codd, to RDBMS's terminology:
The success of relational databases is rooted in the solid mathematical foundations of Ted’s model. Most relational database management systems use SQL to access the database. The versatility of the SQL language is another reason for the success of RDBMS.
SQL and relational databases
SQL is a popular declarative programming language where you specify the tuples to retrieve without specifying the algorithmic steps to retrieve those tuples.
Unlike imperative languages, such as C, where you specify the exact commands to be executed by a computer, SQL lets you focus on the intended result. RDBMS solutions often support the SQL language as a medium to retrieve and change the stored data. Therefore, it is common to refer to relational databases as SQL databases.
When to choose a relational database?
When you can’t afford to lose data
Relational databases are a battle-tested technology that's been successfully used in all IT sectors (from banking to telecommunications, healthcare and hospitality) for decades.
By default and when faced with unexpected software or hardware failures, most relational databases favour the consistency and durability of data over the ability to immediately access data. Therefore, if preserving your data is among your most important criteria then you definitely should consider relational databases.
When the size of your data matters
The relational model enables you to re-use your existing data without duplicating all or part of it in order to accommodate new relations (as it might be the case with simpler key-value or document paradigms). Moreover, most RDBMS are optimised for disk access and can scale well to TBs of data per single server while providing good performance.
When you have dynamic or changing access patterns
The flexibility of the relational model and the SQL language lets you run custom queries over your data while providing decent performance. Most popular RDBMS’ engines are capable of generating an optimised access plan even when faced with complex queries. Therefore, you can run new queries on the fly and delay their tuning for later (typically by creating indexes).
When you would like to centralise your data management
There are undeniable benefits to using the best specialised database for every use case you need to support. For example, we can make a case for using a graph database to manipulate graph data. Yet, multiplying your data stores comes with consistency, cost and operability issues that you need to account for before making your decision.
Mature relational databases support a variety of additional paradigms like key-value and document ones. They can efficiently manage geographical data, unstructured or semi-structured data. They also often provide text search capabilities and are a pertinent choice for analytical use cases. Therefore relational databases can allow you to accommodate a variety of new use cases and paradigms and scale them enough before needing a more specialised database.
The exciting journey of relational databases
1969
The relational model is proposed by Ted Codd as a way to overcome the downsides of early record-at-a-time databases, such as IDS. The relational model decorrelates the physical layout of the data from its logical representation sparking a number of relational model implementations.
1970s
First relational databases: System R, Ingres and Oracle.
1980s
SQL, the de-facto lingua franca for relational data, becomes an ANSI and ISO standard. The standard will continue to be updated regularly with the last revision to date being SQL 2023.
Rise of the relational model: PostgreSQL, DBC 1012 machine, IBM DB2
1990s
Even more relational databases emerge: Microsoft SQL Server and MySQL included. Boom of internet based services.
2000s
Rise of specialised relational databases: SQLite (embedded) and GreenPlum (analytical).
2010s
Popularisation of cloud-based database as a service offerings (e.g. AWS's RDS, Google's Cloud SQL, Azure's managed SQL offerings)
Rise of NewSQL databases; existing and new relational databases start integrating some of the ideas popularised by the NoSQL movement, such as sharding and the document paradigm.
2020s
Emergence of cloud-agnostic database as a service solutions that further simplifies the management of relational databases.
Examples of popular relational databases
At Canonical, we provide services for the most popular relational databases:
We help you build your MySQL as a service.
We help you build your PostgreSQL as a service.
Embedded and replicated database based on SQLite.
Jointly with Microsoft, we maintain and support optimised images for SQL Server on Ubuntu.
Looking for guidance to design your database build?
Relational database resources
Note
"MySQL" is a trademark or registered trademark of Oracle. “PostgreSQL” is a trademark or registered trademark of PostgreSQL Community Association of Canada. Other trademarks are property of their respective owners.
Canonical's database services and offerings are not sponsored, endorsed, or affiliated with the trademark owners.