Airtable review: A drop-dead easy relational database management system
- 25 February, 2016 00:53
Airtable is an online platform for creating, using, and sharing small relational databases. It’s not ready for enterprise users yet (that’s coming), but right now, if you want help managing data for personal use or for your small business, if you need to share that data with others, if you’d like users to access it anywhere, and if you’d love to do all this for free (or next to nothing), then Airtable might be the answer to your prayers.
On your computer, there is nothing to download. Just launch your browser, go to Airtable.com, and sign up. It’s free to get started.
First base: House hunting
You can pick from a couple dozen starter bases for different purposes. (“Base” is Airtable-speak for database.) Or you can import a .csv file or paste column and row data from an existing spreadsheet. But it’s a snap to build a base from scratch, so let’s do that. And since it’s easier to show how this works with concrete examples, let’s do that, too.
Imagine you are in one city looking for a new house and your spouse, having drawn the short straw, is back home packing. On your new Airtable account’s home page, click New Base, give the base a name, then double-click the base’s icon to open it. Three columns are predefined for you: Name, Notes and Attachments. The Name field in Airtable is for a unique description of each row or record, so in this scenario, you’d want to use the Name field for property addresses. Define two or three more columns—price, neighborhood, rating—and you’re ready to go. All this takes, oh, 60 seconds.
One last thing. Before you hit the streets, download the Airtable app to your iPhone and use the app to sign into your new account. (There’s an iPad app, too. An app for Android is in beta now and will be released later this winter.)
Second base: On the phone
Now, when you are out visiting properties, you’ll launch the Airtable app on your phone, open the househunting base you created earlier, click on an empty row, and enter each property’s address, price, etc. Click on the Attachments column, then on the camera icon, and you can take one or more photos of the property and they will automatically be stored in the base. The app integrates well with your phone. If configured properly, addresses can be opened in Maps; phone number values can be tapped to make calls. There’s even a barcode column type so you can scan barcodes.
Now if you visit many properties in the same area, you’ll probably get tired of typing the same neighborhood description again and again (“Vista del Lago Lindo Estates”). So in the record view, click on Customize Fields, then click on Neighborhood and change its field type to Single Select. Save your way out of the base editor. Next time you create a new record, when you click into the Neighborhood field, the value picker will appear and you’ll type in a value only if it’s not already there for the picking.
Notice what is going on: You’re not only entering data (including photos) on the iPhone, you’re editing the structure of the database. Need another column/field, say, Square Feet? Click Customize again, and add a field. Or fields. On your iPhone. You could actually create the base from scratch on your phone, but that would be showing off.
And once you’ve shared your househunting base, your spouse will now be able to take a break from packing boxes, access the base, review what you’ve seen, look at the photos, and write encouraging notes to you in the Notes field. (“Are you kidding me? This place looks like a dump!”) This data gets synced in both directions instantly. (“Hey babe take another look at the price.”) If you wanted to share with your teenager without giving the teenager the ability to edit the base, you’d adjust the access privileges for that share.
Third base: A fundraising banquet
You can even give access to people who don’t have an Airtable account. For example, if you were organizing a fundraising banquet, you could create an RSVP form for your Airtable Invitees base, publicize the url of the form, and let invitees enter their own info (name, phone, attending or not attending, number of guests, etc.).
As the RSVPs come in via the online form, you’ll want to start assigning attendees to tables, and Airtable can help here, too. Add a column to your list of invitees and select Link to another table, then create a new table and name it Tables, as in “tables that guests will sit at”. Now just click into that column to link attendees to tables. Congratulations: You’ve created a relational database—er, “base”!
And you’d probably want at this point to take advantage of a couple of Airtable’s calculation options. A rollup column in the Tables view can keep track of how many guests have been seated at each linked table, and a lookup column in the Invitees view can mirror that total so you can easily see whether there’s enough room left at table 11 to seat Linda Lufkin’s party of five.
Home run: Data basics
Airtable may look like a spreadsheet but it isn’t. It’s a database. Unlike spreadsheets, databases require rigid data structures, so you will have to think a little about how to organize your Airtable base. Will a column be used for short bits of text like names or addresses or long text like memos? Will a column be used for date values, numbers, currency? If the data you’ll put into an Airtable base calls for two or more related tables, you’ll want to think carefully about how to link them.
You’ll also need to give some thought to how you want to view your data. You can’t easily program or script Airtable but you can create different views that filter (find) and display data in different ways. For example, I built a to-do base and made different views that show me only uncompleted tasks, or tasks to be done today, etc. Sound complicated? It’s not. There’s a lot of help available and live support is terrific. And you can undo almost every action, so relax. If it can be done at all in Airtable, it’s usually pretty easy.
Airtable is easy partly because its developers have done a great job of eliminating common relational database headaches. For example, you never have to think about the primary or foreign keys that link tables together. But it’s also easy because it sticks to data basics. The scenarios or use cases I described earlier are, I think, examples of where Airtable shines. Hunting for a house or organizing a banquet, you will appreciate everywhere access and the ability to share your data. In these scenarios, you probably will not need to worry about complex business rules during data entry (like “an order can’t be created unless an account exists and payment info has been entered”). If you’re entering the data yourself, you also won’t have to worry about minimum wage data-entry clerks making careless but costly mistakes. And you probably won’t care about beautifully formatted printed reports.
Of course, there are other scenarios for which Airtable is not such a good fit. I doubt Airtable is going to pull many users away from richer database application development tools like FileMaker Pro. In FileMaker, for example, a developer can write a script that finds uninvoiced line items for a particular account, goes to another layout, sorts and totals those line items, saves the whole thing as an attractively-formatted PDF, sends the PDF to the client via email, then comes back to the records and archives them.
Now Airtable does have a fully-documented API. Some benefits of the API are easily accessible to Airtable users through third-party services. With a Zapier account (not free), you can get dated rows in your Airtable base copied to Google Calendar (or integrate in other ways with a large number of other online apps), and Slack can let you know when your base is updated and help users sharing a base communicate. But using the Airtable API to generate multi-step actions that resemble FileMaker scripts will require programming, and it’s a safe bet that most do-it-yourself users of Airtable won’t go there.
But even without programming, Airtable is amazing. Folks who, for lack of a better option, have been throwing structured data into Excel or Google Sheets or (gasp) into tables in word processing apps, now have the better option they lacked: they can put that data into Airtable, which is an honest-to-goodness database tool. Factor in its cost and accessibility advantages, and to many users Airtable is going to be a revelation.