A relational database is a collection of data logically organized so the information can be easily edited, added to, deleted, and most importantly, accessed. Relational databases, which store data in structured tables, are one of the most popular and easy-to-use types of databases.
You might be familiar with traditional spreadsheets, which also store data in tables. However, unlike traditional spreadsheets, which were originally developed to function like accounting worksheets for financial calculations, relational databases were specifically designed to store information reliably and scalably. Because of this, relational databases offer vast flexibility in terms of what you can do with information.
While databases used to be exclusively the domain of developers, and out of reach for the average person, now, they can be mastered by just about anyone given the right tools. Once you understand the components of relational databases, you can begin to envision how to construct and use them for your particular needs—whether you’re hoping to manage a project for a large team or simply build a record-keeping system that will be scalable and help eliminate mistakes.
Let’s start at the top.
A database is the container for all your data. (In Airtable, we simply call it a base.)
Look inside a database, and the first thing you’ll find is at least one table.
What is a table?
Tables are the main building blocks of relational databases. Each table is a data set composed of records and fields, and represents a single subject.
For instance, if you’re an event planner, you might have a table for events, a table for venues, a table for vendors, and a table for clients. Each of these tables contains all of the relevant data for that specific subject: for example, the event table might contain dates and times for each event. Inside a table, the data is arranged into records and fields.
Nerd alert: the technical term for a table in a relational database is "relation." In 1970, Dr. Edgar F. Codd first proposed the relational database model in a paper titled “A Relational Model of Data for Large Shared Databanks.” His model was heavily based on mathematical set theory, and the name “relation” for a table is based on terminology from set theory. A common misconception is that the term “relation” comes from the fact that the tables in a relational database “relate” to each other.
What are records?
In a relational database, a table represents a single subject; a record is a unique instance of that kind of subject. For example, in an event planner’s database, each record in the events table represents a different event (“The City Museum 15th Annual Fundraising Gala,” “Equilux Holiday Party,” etc.); each record in the vendors table represents a different vendor (“Objectively Edible Catering,” “By the Bouq Flowers,” etc.); and so on.
Nerd alert: Any mathematicians in the house? You might know records by another name: tuples. A tuple is a finite ordered list of elements.
If you’re familiar with traditional spreadsheets and their grid format, you might think of records as “rows,” and records are in fact often visually represented as rows in a grid. However, there are two key difference between rows in spreadsheets and records in relational databases:
- The order of the records within a table in a relational database does not matter. This means that a relational database can give you a lot of flexibility when it comes to how data can be displayed. You can reorder records, look at a specific filtered subset of records, or even visualize your records as calendars, charts, or kanban boards: no matter how you choose to look at the records, the underlying data in the table remains the same.
- Every record in a relational database must be identifiable by a unique value. Because every record is uniquely identifiable, you can avoid duplicate data, and just reference that single unique record whenever you want to get data on a specific subject.
These two facts are what make a relational database capable of representing the relationships between different people, objects, and ideas. Records can be added, deleted, and modified, and when a change is made to a record, it shows up everywhere that record is being referenced.
Think about a database that includes customer information. You use this information in various places within your business operation.
For instance, you probably use it for marketing, where it’s important that email addresses are correct. You may even go so far as to target your marketing to each customer’s state. A customer who has made a purchase or is actively enrolled in a service sees a different marketing campaign than a prospective customer might. It’s therefore important to know when a customer’s state changes—for instance, if they make a first purchase. The customer information is also used by your fulfillment operation and your billing operation, so one record is pulled into multiple places.
Within a database, customer records are updated as needed—sometimes automatically, when a certain action occurs, such as a purchase. Anywhere that customer information is referenced, the change is instantaneous. As you gain and lose customers, and as customer information changes, it’s always up to date everywhere you use it. The same goes for any kind of information you transact with in your business.
What are fields?
In a relational database, a field represents a characteristic of the subject of the table. For example, in an event planner’s database, the “Venues” table might include fields like “Street address” and “Maximum occupancy,” whereas the “Vendors” table might include fields like “Vendor type” and “Vendor Phone Number.” When a table is displayed as a grid, the rows are records, and the columns are fields. Another way to think about records and fields is that records are like nouns, and fields are like the adjectives that describe those nouns.
Nerd alert: to be precise, fields are referred to as attributes by engineers.
Relational databases allow you to create rules for what kind of data can be entered into each field of a record—you can’t, for instance, put words into a field designated for numbers, or put an invalid email address into a field designated for email addresses. Holding information in fields and records rather than static rows in a spreadsheet makes it much easier to work with and keeps the integrity of the data intact everywhere.
What are keys?
Here’s where it gets exciting. In a relational database, there are special types of fields called keys. Keys connect the dots: they create relationships between records across multiple tables.
Within a table, the primary key field—or as we call it at Airtable, the primary field—holds a unique identifier ID for each record. The primary field cannot be deleted, and must contain unique information. For this reason, it can’t be formatted as a checkbox or a select field. It must contain a unique identifier—words or numbers.
A foreign key field is a field within one table that points to the primary key field of another table. For instance, in your table of customers, you might have a field containing a unique identification number for each customer, and that ID number will be the primary key field of the customers table. When that ID number is pulled into another table—such as a customer orders table—it shows up in that table as a foreign key field.
What are table relationships? What are the different kinds of relationships?
The primary and foreign key fields create relationships between the different tables. To say that tables are related is to describe how they share information. When information is updated in a record, it’s automatically updated in every table that displays that information.
With databases, the relationships between types of information can take a few different forms. There are three ways of describing such relationships.
The simplest type of relationship in a relational database is a one-to-one relationship. Two tables could have a one-to-one relationship, but more often than not, if you have a list of items that corresponds exactly to another list of items—for instance, a customer and a customer ID—it’s easier to display both sets of information in one single table:
One-to-one relationships are nice and tidy, but alas, they are not as common as one-to-many relationships, where for each element in a list, you may be able to associate multiple elements from another list.
For instance, if you work with a company, you might have several contacts at that company. While each representative is attached to just one company, the company can have multiple representatives.
Now, things get crazy. Consider your list of customers and your list of products. A single customer might buy multiple products. And a single product is certainly purchased by multiple customers.
In this kind of case—probably the most common scenario—there are many ways to cross-reference information, and for this reason, a database that pulls records into various formats is the way to go. The trick to success is in the planning of the database, or the schema.
What is a database schema?
As you architect your database, you create a structure with rules. This is called a schema. It’s the blueprint for your database that describes what kind of information it will hold, and how that information will be related.
A database schema allows you to enforce rules around data types. For instance, you might decide that a field only has three possible options, which you’ll input as strict choices. Or you might indicate that a field can only take text, or only numerals, or only an image file.
A database schema also enforces any rules you might have around relationships between tables: for example, if a school administration was using a database to plan classes, they could implement rules like "Each student must be signed up for at least two but no more than six classes," or "Each class requires at least one instructor."
These types of strict rules will keep integrity within your data and will help you display data in various ways. For instance, with Airtable, you can display your data in different views and switch seamlessly between those views: grids, calendars, kanban boards, and galleries. Each view displays and lays out information from records to different ends.
You can also act on your databases in tangible ways—for instance, creating Gantt charts or documents from database information. The ways you can view and work with data are flexible, but underneath the agile tools lie stable datasets with lots of integrity.
With what you now know about relational databases, how can you build them into your business—and more importantly, how can they help you build your business?