Databases are the cornerstone of any business; they're used to track purchase orders, catalogue customers and manage employee payrolls. On top of that, basically every web technology is built on a back-end database.
The most common form of database within IT is the relational database. These are coded using SQL (pronounced 'sequel') which stands for Structured Query Language. These databases require specialised software to manage, examples of which include Oracle Database, Microsoft's SQL Server family, and PostgreSQL.
Relational databases were first developed in order to standardise the way that databases were constructed and maintained. Prior to this, the way databases were structured and navigated often differed from one to the other, making them hard to develop applications for without being deeply familiar with the database in question.
In order to make this task - and the general process of working with databases - more efficient, relational models were developed to provide a universal, standardised format for databases. These were easier to maintain, more versatile and quicker to learn how to work with.
How do relational databases work?
Relational databases are comprised of multiple interconnected tables which are linked by a shared value. These shared values are identified by 'keys' - the column or columns within a table which contain values which are shared between multiple tables. These keys help the database understand which row of values to pull from each table when handling queries. The primary key is usually the first column within a table, although a table can contain multiple subsidiary keys, or 'composite keys' comprised of multiple combined columns.
Let's look at an example, from a fictional restaurant delivery company that wants to offer UK customers who have been premium subscribers for over a year a voucher for their favourite food as a reward. Their database contains two relevant tables - one with customers' personal details, and one with their subscriber information, as shown below.
Personal details
| Email address | Name | Surname | Country |
| John_Smith@domain.com | John | Smith | UK |
| j.doe@unknown.net | Jane | Doe | FR |
| JonesSteve@address.org | Steve | Jones | US |
Subscription details
| Email address | Membership tier | Membership length | Favourite restaurant |
| JonesSteve@address.org | Premium | 6 months | Thai Dyed |
| j.doe@unknown.net | Free | 3 months | Pizza Planet |
| John_Smith@domain.com | Premium | 18 months | Burgers Ahoy |
In order to establish which customers are eligible and to send them the offer, the company needs to establish their name, email address, country, membership tier, the length of their membership and their favourite food. They can do this by using the database's key - which in this case is the email address - to cross-reference the two tables with one another.
This allows the database to establish that, because he has been a premium member for a year and a half and lives in the UK, John Smith is eligible for the reward, as well as allowing it to automatically send a personalised email to him containing a voucher for his favourite burger restaurant.
Relational database schemas
Relational databases are organised according to specific schemas - predefined rules governing how tables are laid out, including what data is contained in each column and the order they come in, as well as which columns are used as keys.
Schemas can be coded in SQL or laid out in charts and graphics. The schema's specifics will differ from database to database, but the format is standardised enough that it's easy to go from working with one schema to another, making them simpler to develop applications for.
Advantages of relational databases
The primary advantage of relational databases over earlier non-standardised methods of database architecture is the high degree of consistency they offer. Relational databases are both internally consistent - ensuring that data is easy to find, because it's formatted and laid out in a uniform way - as well as being much more interoperable with each other due to their use of pre-set schemas.
Relational databases are also rules-based, meaning that you can manage and edit large databases by creating global parameters and applying them to every record and table within a database. Additionally, because the databases themselves are independent of the physical storage on which they reside, database architects can alter the infrastructure the database runs on without affecting its logical integrity.
These databases also have certain advantages over newer database architectures such as NoSQL (which we'll discuss further in a moment). Relational databases tend to be more stable and consistent than their non-relational counterparts, and they're often better-suited to complex queries. The relational model also benefits from a much greater wealth of support resources, information and skilled professionals, due to its longer history.
Relational database alternatives
In recent years, the dominance of the relational model has been challenged by the emergence of non-relational 'NoSQL' databases, typified by providers like Couchbase and MongoDB. NoSQL databases (predictably) do not use SQL, and are instead built using object-oriented programming languages like Python, Ruby, Java and C++.
These databases are predominantly built to handle massive scale, and have found particular popularity as a tool for big data storage and analysis. The main advantages NoSQL offers over relational databases are that NoSQL databases can be expanded horizontally by adding more nodes of commodity hardware, and that they are designed to allow unstructured data, whereas relational databases demand rigid formatting.
