I Finally Understand Databases Because of the Free Airtable App

You know I’m a huge fan of Excel and all things spreadsheets. Pivot tables, complex equations, they all make my heart go pitter-pat. But I’ve never really understood databases. I can explain what they are and why they’re not like spreadsheets, but only at a very surface level. I haven’t ever grokked them because I never had access to a database program or found a problem they might solve.

On ScreenCasts Online episodes 546 and 548, Don McAllister introduced a database application for the Mac, iOS, Windows, and Android called Airtable from airtable.com/….

Airtable is under the freemium model. While they have Plus, Pro, and Enterprise pricing, Airtable is really capable for the home user for free. Don’s tutorials got me really excited about using Airtable, but for the life of me I couldn’t figure out what problem it could solve for me. While Don taught the mechanics of Airtable, I still didn’t “get” the purpose of a database.

Airtable for Interviews

I was determined to figure it out though. After Steve and I went to CSUN, I thought I might have a very simple problem it might solve. When we go to a conference and record video interviews, we have to keep track of a lot of information. A few years ago I started putting the information into a Google Spreadsheet so it was easy to share.

It started out pretty simple. For every interview, I put in the essential information from the person’s business card. Name of the product we talked about, name of their company, interviewee’s name and email and the website for the company. But then we noticed sometimes we’d also get the card for the marketing person. There are two more columns.

Then Steve added some columns for him to keep track of the order in which they’d been recorded, and the order in which he would produce them, which is often not the same. He always puts in the description of the videos where the interview took place, so he added columns for the event and the venue. Then he added a column so he could note when he’d posted the video.

You may have noticed that we spam everyone when we post a video. So that meant columns we’d put X’s in to show when we’d each Tweeted, Facebooked, Google Plus’d the content. Oh wait, I need a column to mark off when I send an email to the person I interviewed. One more…when I actually play the audio on the podcast I have to check off a box.

Anyway, you get the point – by the time we were done, there were 21 columns of information. Since Steve’s data was all on the left of the spreadsheet, I was frustrated that I had to scroll all the way to the right to get to the information I cared about.

I wasn’t sure Airtable would be able to help with this but I just had to try. Turns out to be kind of a dumb example for a database, but it did solve many of my problems. I want to take a moment here to give a shoutout to NosillaCastaway Jin-Oh Choi who got on a Discord screen-sharing session with me to help me get the basics under my belt. He really got me excited to keep learning.

Airtable Basics

I’m not going to give you a full tutorial on Airtable but some terminology is necessary. Every new thing you’re going to do with Airtable is called a base. Inside a base, you have tables. Think of tables as looking like tabs in a spreadsheet (but they don’t act like them). Inside a table, instead of having rows and columns, you’ve got records and fields.

I downloaded the Airtable Mac app and the iOS app on my iPad and iPhone and got to work. I created my first base and called it CSUN 2018 Interviews. Inside the base, I created a table called All Data. Sadly, this base will only have one table, which is why I said it didn’t really crack the code for me on what a database is for. Spoiler, I’ve got a second example that’s starting to ravel the mystery for me.

The first column of your spreadsheet is kind of a sacred field. I started by importing our giant spreadsheet of doom (™️Donald Burr) and that was a big mistake. The first column was the recording number Steve uses. That made a big mess of things. I started over and made the first field the company name. If I ever figured out what to do with the other tables, that company name would be the logical thing to use as a cross-reference.

I then made columns for all of the other gunk we needed to track. The fun began when I realized how many different kinds of data you choose from for the fields. For example, in our spreadsheet we had a column called Twitter, and then one, two or three x’s in that column would tell you whether we’d tweeted from all three of our accounts (I have two with podfeet and nosillacast and Steve has only one).

In Airtable you can customize a field to be multiple select, and then put in only valid selections. I put in the three names, and now when we go to that field we just tap the plus symbol and then the account from which we tweeted. Airtable automatically makes them different colored bubbles so it’s really eye-catching and clean.

For things like Emails sent, or podcast played, I just made them checkboxes. I set the fields that were websites to only accept valid URLs, and emails could only be valid addresses too.

My table was very pretty and easier to mark off what had been done, but I still had the problem of a very wide page with a lot of glop I didn’t care about, and the initial data entry of the info about the vendor was still tedious.

That’s when I discovered Views. Views allow you to see the same data in the table but in a format that’s easier for you to work with. I found out that if I created my own view, I could simply tell it not to show me all those columns Steve needed but were just in my way. Now I have a compact, clean view to work in and he can still have his giant, wide view.

I also discovered Forms, and they changed how I feel about doing the data entry during and after a trade show. I created a Form called Business Card Entry Form, and I was able to drag and drop only the fields I needed up front into a nice, vertically formatted form.

Forms are designed to allow you to have someone else enter data but not have access to your database. So after you create a form, you post it somewhere online, or just send the private URL to people. In my case, I only shared it with myself, but when I showed it to Jin-Oh Choi he was thrilled. He was having people send him data in spreadsheets, and now the form will allow those people to send their data right into his base. It was great to have discovered something he didn’t know!

Being able to access Airtable from my Mac, iPhone, iPad and even just through a web browser is fantastic to keep things up to date. While Airtable was pretty and made the work easier and more pleasant, I knew I had just created a glorified spreadsheet. It was time to take things up a notch.

Airtable for Bathroom Remodel

Steve and I have been talking about remodeling all of the bathrooms in our house for ages, but we have trouble with the “start” part of a project like this. Dorothy (you may know her as Maclurker from the live chat room), has actually torn half of her house down and had it rebuilt in the time we have thought about maybe fixing to make a plan to go look at some faucets.

At her urging, we finally met with her contractor and designer. As we walked through the different rooms and they threw out remodeling ideas about them, I furiously scribbled down notes. I learned whole new words like what a sconce is.

I started thinking about all the decisions we would have to make on so many different things. There was floor tile and shower tile and overhead lights and sconces and faucets and sinks and tubs and toilets! And I was willing to bet that in a lot of cases, we’d pick the same thing for two different rooms. Steve and I really not imaginative enough to pick two different types of overhead lights and I was betting the tile would end up the same in two rooms.

I thought maybe I’d come up with the three-dimensional problem that a database could solve. I had great fun experimenting with how this could work, and I’ll spare you most of the ideas that didn’t pan out.

I think it’s about time we declared a problem to be solved, don’t you? I wanted to be able to look at one page and see for a single room, the status of all of the decisions we’d have to make on selecting and buying stuff. I’d want to see the list of components down the left column (e.g. toilets, paint, tile), and then over time, I’d hope to see fields completed for brand, name, a picture of what I’d chosen, how much it was going to cost, and its status. Had I made a decision? Had I ordered it yet? Had it been delivered or finally installed?

I thought at first that I’d not only have separate tables for each room, but separate tables for each type of component, but that didn’t actually work. Instead, I created one giant table called Components.

The Components table has fields for model name, type (faucet vs sink vs paint), material, price per quantity, etc. Airtable has so many fun field types, and I employed a lot of them here. There’s a star rating field type which will be very useful for eliminating dumb things we see. You can add attachments to a field, so we can actually put in pictures of each item. My memory for names and models of things is nearly non-existent so a picture will be super helpful to jog my memory.

Now back to the Room tables. In each room’s table, I can create a field that is linked to another table. This is FINALLY the value of a relational database. In each Room table, I put field which is liked to the components table. Then within a given record, I can actually do a lookup of a specific component.

So in my Downstairs Bath table, I can have a record called Faucet, select a specific model name from a drop-down that is automatically pulled from the Components table. Once I do that, the rest of the fields will auto-populate, calculating the total cost and showing me a picture for example. If the table for Components is updated that information is automatically updated in the Room table.

The one thing I didn’t like about this approach was that my Components table was going to be this giant pile of glop all mixed together. I know I could sort by field so the faucets were near each other and the sinks were together, but it seemed so messy.

But then I saw the Group button in the menu bar and I squealed with delight at what it does. Group does what the name implies, it groups things on the table by whatever field you request. I grouped by Type, and Faucets were nicely separated visually from Paint and Tile.

Sharing

Not only does Airtable have all of these great features and allow you to edit and view from iOS and your Mac (and Windows and Android and the web), sharing of Airtable bases is built in, all still available in the free model. Simply hit the share button, put in the person’s email and they get an invite to come play with you.

Steve and I can enter information and create views and grouping the way we like it and not affect the other person’s view of the information in the base.

Bottom Line

I have absolutely only barely scraped the surface of Airtable, and yet it has helped me understand how relational databases work. I understand now how the different tables can relate to each other and yet are standalone entities. It’s still a lot like a spreadsheet, so it’s familiar and yet far more friendly for this type of task.

I mentioned up front that the features I’m using are all free, so I thought it would be good to close the discussion by telling you about the limitations on the free version of Airtable.

For free you get unlimited bases but each base is limited to 1200 records per base, and 2 GB of attachments. If I ever get close to that much data in a base, you’ll know I’ve slipped fully down the rabbit hole into madness.

For free Airtable will save 2 weeks of revision and snapshot history as well. This is great to know because Airtable doesn’t nag you constantly to confirm changes. It’s great to know you have a safety net.

Airtable has what they call “essentials,” and from the free version to Enterprise, they are all available:

• Rich field types as I’ve described
• Modifiable views including grid, calendar, form, kanban, and gallery (which I didn’t even go into)
• Apps for web, desktop, iOS, and Android
• Real-time collaboration and commenting – I didn’t realize the collaboration was real time until I saw this in the pricing!

If you’re interested in even more advanced options, check out the link in the shownotes to the pricing page.

I’m so excited about Airtable and using a relational database that I’m actually looking forward to the bathroom remodel project!