Airtable offers a revolutionary new approach to relational databases - designed to appeal to business users accustomed to working with spreadsheets. In this article I am going to look at the core Airtable app - and in a subsequent articles I am going to look into how the basic app's capabilities can be extended with Zapier and more advanced formulae.



What is Airtable

Airtable is a cloud database aimed at business users (rather than database specialists) - the company's stated mission is to "democratise software creation by enabling anyone to build the tools that meet their needs". In this review we will be measuring the product against that objective and getting an understanding of who the service is for and how well it meets their needs, both now and into the future as their experience grows.

I am going to use the example of a simple HR app…



When you open Airtable you will see a screen like the one above, where your databases (bases in Airtable parlance) are displayed, and from where you can create new ones. New 'bases' can be created empty, created by loading data from a spreadsheet - or from a number of templates of which there are quite a few covering a range of needs. These are an excellent way to familiarise yourself with Airtable even if there isn't one which will meet your needs precisely.

Basic Tables and Field Types

For this review, we are going to use a base I have ben working with called KasPer Lite. HR applications present a range of challenges which are common to a wide range of business scenarios and offer potential to explore how Airtable handles the relationships between information such as Employees, Jobs and Departments etc.


In the example shown above, I have a 'View' called Main View which is displaying all my employees grouped by department and job. Note how similar to a spreadsheet this presentation of data is. Each table in Airtable has Primary field - this is the leftmost field in the table and represents the value that would be displayed in Links to other tables (more later) - although it actually contains a unique key for the record it is typically set up to display more useful information (either as an input field or a formula combining values from others). In many case, I find this field is best configured as a Formula field - in this case showing the First Name, Last name and Employee Number fields:



Clicking Customize takes you to the field editor

There are a wide range of field types available including all the basics plus some which are concerned with the Relationships in your data such as Links to other Tables, Lookups from linked tables and Rollups which calculate values based upon linked records in other tables. There are also URL fields, checkboxes, single select and multi select fields, etc. Suffice it to say that there are fields types to meet most requirements and comprehensive summary and formula features to extrapolate from your raw data.


Other Ways of Viewing Data

In addition to the Grid view above (in this case shown Grouped) there are other types of views available:



You can create as many views as you like from each table and they are shared between all users. Within views you can determine which fields are displayed - and records can be 'Expanded' into a popup form for editing (you can also edit within the table for Grid views).



The expanded view also shows a history of changes to the record and allows social interaction in respect of the record - using the typical @ mentioning to send comments about the data to other users. I particularly like this feature as it encourages team members to collaborate around your data.

Gallery views display data in individual cards (which can be customised):





With this type of view you can choose which fields to display and also which field should be the Cover field (this is most likely an image as in this case).

Finally, in terms of views used within Airtable, we have a monthly Calendar type view - here showing Leave Dates:





Capturing Data Externally

In addition to the views used within Airtable, there is also a facility to create data entry forms for embedding in your website or to which you can create direct links. Here is an example which could be used by employees to enter leave requests:



This form could be embedded on your company's intranet or simply available as a link. You are able to add help text to individual fields to make their use more friendly as well as choose which fields to include.


Relationships Between Data

This is a 'relational' database and the way links between related data are handled is very important. Airtable allows you to create these links very easily - here, for example is how the employee records in our app are related to the Jobs they occupy. Simply creating this Link field creates the relationship in the database and a corresponding field in the Jobs table:



You can see how you can choose whether the link allows you to create one link or many - in this case I have chosen to only allow an employee to have one job. However, if we look at the Jobs table you will see that the corresponding Employee field can contain many employees - this is set up like this for you if you create the link from the employee side and is an example of a one to many join (to use the database terminology):



My only criticism of Airtable's implementation of Linked tables is that you cannot specify what happens if records are deleted. For example (still from HR) if you were to delete an employee, I would expect the database to also delete information related to them such as their Leave records. In the case of Airtable, at the moment, this is not the case - there is no way to set up Cascade Deletes of this sort or, indeed, a method to prevent deletes, which can be important in other scenarios. The only way to deal with this situation is to manually delete the associated 'child' records - otherwise they remain in the database and could cause confusion.

Having said all that, it is all very simple and user friendly to set up.


Formulae, Lookups and Rolling Up Data

We've seen how you can create formula fields in Airtable and these can be quite complex. You can also include fields from Linked tables using Lookup fields and also summarise data from Linked tables using Rollup fields. In the following screen shot there are two examples of Rollup fields (totalling Holiday this Year and Sickness from the Absences table):



I am showing the definition of the Holiday rollup here which simply sums the Holiday this Year field from the Absences Table. This is, in itself, a calculation which takes a total of Absence Days between the Start of the leave year and its end. We'll look at Absences in more detail in the next Article where we will look at how to build more advanced calculations and also use Zapier to automate some processes.

Overall there is a good range of tools in Airtable to analyse your data - but there are no Queries such as you would find in databases such as Access or MySQL. Your calculations are based upon Formulae, Lookup and Rollup fields. This approach is more flexible than it appears at first and would be very suitable for people new to databases - while more seasoned users will find ways to achieve most things.


Reporting and Printing

Users are able to set up as many Views as they want for each table and so there are many ways in which data can be analysed. It would be great to see some graph Views (bar charts, pie charts etc) available with the ability to summarise data as you can in Grouped grid views. Hopefully it is just a matter of time until this is available.

All Views can be printed (including the Expanded views which allow printing of the one record you have open). Accessing a views print option is through the […] button - although it is less obvious for Expanded records where you click the small down arrow beside the record's Primary field content in the title bar:



There are some limitations - for example it would be nice to see the Grouping assigned to a Grid view reflected in the printed result. If you need to analyse your data further, then it can also be downloaded in CSV and you can also integrate your base with other services through Zapier which would enable you to send emails directly or sync data with services like MailChimp.


Usability

Many people will love the look and feel of Airtable - it seems familiar (it looks like a spreadsheet after all) and you can easily build yourself a range of views to present your data in a number of ways. I am personally a little concerned about how easy it is to change data by accident - this is really a side effect of using a spreadsheet style interface - and would like to see features to restrict some users from accessing things or changing things. At the moment, security options are fairly limited with users able to edit records also able to change your carefully created views.


Web, Mac, Windows and Mobile (iOS and Android)

Yes, Airtable is a multi platform solution (with the data in the cloud) and has clients for Web, Mac, Windows and mobile! All the screenshots in this article have been taken from the Mac app (which looks almost identical to using Airtable in a browser). There are apps for iOS and Android and these do have some differences (records are always shown in a list of tiles for example) and some features are not available (formula fields). However, having a suitably designed user interface able to access all your data from wherever you are is great.


Data Security and Geography

Airtable takes automatic snapshots of your entire 'base' periodically and also allows users to do so manually (a good idea before making any significant change) - you can also make a full copy of a base at any time. As mentioned above, you can also download data in CSV format.

Airtable's data is, I believe, held on servers in the USA. This can be a problem for users in other jurisdictions such as Europe and we would recommend that users consider this in light of their local data protection regulations (in the case of the EU this is new EU General Data Protection Regulation (GDPR)).


Conclusion

Airtable is a very accessible database application for newcomers to the database world or anyone needing to create a solution quickly. Despite first appearances, the system provides quite comprehensive ways in which to display and analyse your data. However, I am a little concerned by the lack of relational integrity in the database (no way to enforce Cascade Deletes for example).

The user interface encourages you to work with your data, presenting it in different views, filtering it and sorting and grouping it in different ways. This is great in a small team - but could lead to confusion in a larger roll out where control over user's actions becomes more important.

Finally, you can start with Airtable absolutely free! You will only need to pay for the service once you have a base containing more than 1,200 records - and then the cost is a still reasonable $12 per user for up to 5,000 records.

Despite my few concerns, I thoroughly enjoy working with Airtable and would recommend you give it a try!