This article provides a quick overview of relational databases, so some experience with programming and knowledge of data structures is necessary. The article is also helpful for non-technical users of databases, including website owners.
A relational database is a system that organizes information into neat, orderly structures. A relational database management system (often abbreviated RDBMS) accommodates large numbers of records, provides data to many users simultaneously, and serves as a central data repository for application programs. A database eases the task of data management, making information more accessible, secure, and useful.
While it would be more accurate to refer to relational databases as RDBMSes, we will stick with the more colloquial shorthand “relational database”, or sometimes just “database”.
Why Use a Relational Database?
Most programmers deal with the nuts and bolts of saving and retrieving data files, details that can be complex and cumbersome. Although any good software developer might be able to create data-management code from scratch, reinventing the wheel isn’t necessary, particularly when working with a program that is designed to work with a database. The database handles all the low-level details of data management, retrieving data efficiently and reliably. Databases also have robust, sophisticated security features, allowing appropriate levels of access for administrators, customers, and many other kinds of users.
A relational database stores data in basic elements called fields or data items. A data item is a specific piece of information, such as a zip code, a phone number, a credit card number, or a ship date. Each item is defined in terms of the type of information stored in it, such as numbers, dates, or text. A warehouse record, for example, might include an item cost, which is a numeric data type. The distinction between types is important because the warehouse owner might want to find a total cost by adding individual costs together. A database can’t add text character fields, but it can add numeric fields.
A data table is a useful grouping of data elements. A customer table, for example, consists of elements such as a customer ID, name, phone number, and address; each record in the table has data representing one customer. Most databases have several tables organized by a common purpose; for example, an engineering database might have tables for parts, drawings, materials, and suppliers.
Indexes and Keys
A database administrator can designate some of a table’s fields for high-speed lookups; these fields are called keys or indexes. If a table has no indexes, the database must read every record, one after the other, to find a particular one. For larger databases with millions of records, this process can be prohibitively slow. Setting aside a customer number as an index, for example, tells the database to permit fast lookups by customer number, cutting search times to a fraction of a second.
Common Elements and Linking Tables
The “relational” part of a database is its ability to relate, or join, information from multiple tables. In most databases, some tables have one or more elements in common, such as a customer number that is found in both the customer table and an order table. Although a customer has only one record in the customer table, that customer may have dozens of records in the order table–one for each purchase. Linking tables together with common elements creates a temporary “virtual table” that contains useful combinations of information. For example, a manager wants a list of customers and the last date they bought something. The name is in the customer table, but the date is in the order table. By temporarily joining the customer and order tables, the manager can obtain both pieces of information.
Structured Query Language
Most relational databases use the Structured Query Language (SQL, pronounced either as the letters, “ess-kew-el”, or sometimes as “sequel”), a software language that lets programmers build databases and access the information in them. In SQL, the SELECT statement retrieves information from databases, UPDATE changes data, and DELETE removes records from tables. A simple example of a SELECT that lists all records in a “customer” table might look like the following statement:
SELECT * FROM customers;
To see a few more basic SQL commands and to get a feel for how the language structures queries, we have an introductory guide as well.
Vendors and Major Products
Examples of commercial relational databases include Microsoft’s SQL Server (MSSQL), Oracle Corporation’s Oracle and IBM’s DB2. MySQL, MariaDB, and SQLite are open source databases, available for free (paid options of these open source implementations are also available that include various levels of support).