Primary key and foreign key


#1

Hello everyone, I have just discovered Airtable from @Tim_Dietrich’s website when I checked out some of his FileMaker work. I was shocked by Airtable! Great online rapid solution, dreams finally come true. Thanks, the airtable team. Also, thanks Tim for good recommendation.

Playing around with airtable for a couple hours, I am really impressed by its potential. However, as a new comer from Access/Filemaker, I am figuring out how to deal with the primary key and foreign key. It seems that, if the “name field” is unique, I don’t need to have a specific field for a “key”. Would you mind to share your experience of using the name field? Is it impossible to change the name field?


#2

Hi Karl!

Welcome to Airtable - and I’m glad to hear that you’re enjoying it.

The “name” field can be a little confusing for those of us coming from database platforms such as FileMaker. Normally, we have to explicitly specify a primary key on a table, so that we can setup relationships to it from other tables.

With Airtable, all of that is done for you. Behind the scenes, each record is assigned a UUID, and it is that value which Airtable really uses as the primary key. You won’t normally see those values in the Airtable interface, but if you poke around in the API, you will see how they are used.

The “name” field is sort of like an alternate primary key, which, as users, we can see and use to identify records in a more user-friendly way. You can change the name field if you’d like. For example, you can change the column’s name, its type, and so on. You can even setup a name column so that it acts in a way similar to the auto-increment fields that we often use in FileMaker databases.

In Airtable, when you relate records from one table to another, it is the “name” field that you use to select a related record. (Again, you’ll never see the primary keys that Airtable is really using.) The nice thing about this is that you can change a record’s name value, and any relationships that the record is involved in will be maintained.

I hope this all makes sense. If not - I apologize. It’s been a very long day! Feel free to reach out to me either here or via email (timdietrich@me.com) if you need anything else.

~ Tim


#3

Hi Tim,

Thank you very much for the clarification. Your information is very helpful. The APIs sound really exciting. I will try them later. At the moment, I am working hard to move my stuff from Access/Filemaker to Airtable.


#4

Hi Tim -
The NAME field is often the field I want to use to link from one table to another, but since we can’t specify NAME in a link, do we have to duplicate the NAME column just to link?
Example:
Vendors, Vendor applications, and Vendor Schedules are all 1-to-1 relationships, each having NAME as essentially the primary key. How to link these? I don’t want to have them all in a single table.
Thank you!
Martha


#5

Hi Martha,

If you really want all of those to be in separate tables, I would suggest that for the vendor schedules table, you make the second column a link to the vendors table, then make the primary column a formula that return the value from the second column.

(If all of the relationships are 1:1, you could also just put them all on the same table and make different views to show/hide the application and scheduling information.)


#6

Martha,

If you’re coming from FileMaker Pro (like Tim or like me), Airtable’s handling of relationships takes a little getting used to. Once you get used to it, you’ll probably find as I have that a lot of stuff that takes some work in FileMaker is easy as pie in Airtable. Of course, the opposite is true, too. Horses for courses, as they say. :slightly_smiling:

.


What the Name field is

The Name column is how Airtable protects casual users from the abstractions involved in database design (like ‘entity’). Unfortunately, calling this column ‘Name’ by default leads, I think, to some confusion.

Whether you’re working in Airtable or FileMaker (or any other database system), within a given table, each row should represent a unique instance of some thing (‘entity’). The unique primary key value for each record guarantees this uniqueness but doesn’t in any way characterize it. If you were able to see it, the primary key 03549674511 would tell you that this record is different from another record whose primary key value = 03549674512, but the key values do not tell you how one record differs from the other (more precisely, how the thing one record refers to in the real world is different from the thing referred to by the other record). In FileMaker, you could create a table that had just one field, the primary key field. This would of course be pretty pointless, but it’s possible. Airtable simply doesn’t let you do that. Instead, Airtable takes care of the ID/primary key for you invisibly, and instead asks you to answer the question “What Is It About This Record That Makes It So Darned Special?” Actually, that’d be a better name for the default column than ‘Name.’ If it weren’t so long.

Anyway, if the entity the table represents is people, ‘Name’ might not be a bad term for the specialness descriptor, especially if your data set is small. But if the table represents an entity that’s not personal, then the column name ‘Name’ can be misleading or confusing, for newbies.
.


What the Name field is NOT

So that’s what the ‘Name’ field is. It may be helpful to think about what Airtable’s ‘Name’ field is NOT.

  • As I suggested already, it’s not really a name field. Luckily, you aren’t stuck with either the single line text data type or the column name ‘Name’. For example it can be a Phone Number data type and you could rename it ‘Phone Number’. It could be a long text column. It could be a formula column. And even if each record in the table represents a person, the ‘Name’ field might more usefully be used to store something else. You might make SSN the primary descriptor, or StudentNumber or something like that.
  • It emphatically is NOT the primary key for the table or even a proxy or alias for the primary key. As Tim pointed out, Airtable handles all the key values invisibly.
  • The ‘Name’ column doesn’t have to have a unique value. If you don’t want to store SSNs in your table (for sound reasons) and you don’t have anything else like drivers license number or student number, then you can create 47 records with the value “Kevin Bacon” in the Name column. Of course, if you do so, then, as far as Airtable is concerned, those are 47 unique and different people all coincidentally sharing the name ‘Kevin Bacon’.
  • And while I’m at it I guess I should emphasize that the Name column is not ‘Column A’ in a spreadsheet!! This is where Airtable’s spreadsheet analogy might confuse some users who are familiar with spreadsheets but don’t know anything about data modeling. In a spreadsheet you might make a list of orders placed by customers, and you might put the customer name in column A, the order date in column B, the (unique) order number (if there is one) in column C, etc. If you converted that spreadsheet to a database and you knew what you were doing, you’d understand that column C ‘Order Number’ is the unique defining value, so you’d create a table named ‘ORDER NUMBERS’ and the Airtable ‘Name’ field could be renamed ‘Order Number’.

.


One-to-many (i.e. normal parent-child) relationships in Airtable

In Airtable, because keys are completely hidden from you, you don’t have to think about what’s a primary key and what’s a foreign key, at least not in those terms. You should however think about the cardinality of the relationship, that is, whether it’s one-to-many, many-to-one, one-to-one or many-to-many.

In FileMaker, you control the cardinality in both directions when you relate two tables. On the other hand, in Airtable, cardinality is controlled one way at a time, when you configure the relationship in the configure options for the related column. You do this by checking or un-checking ‘Allow linking to multiple records’. So in the INVOICES main view, you’d check that option because each Invoice record may have multiple linked line items; while over in LINE ITEMS main view, you’d want to make sure you un-checked that option for the ‘Invoice’ column, because each line item should be linked to only one invoice.

This is important because if you don’t — i.e. if the Invoices column on the Line Items view is configured to allow linking to multiple records — there’s no way in Airtable right now (Feb 2016) to prevent user from creating a line-item directly and linking it to multiple invoices. Which would be bad.

.


Many-to-many

For the record, although you have to be in a view to configure these linked columns, a change made in one view will automatically affect the same linked column when it appears in other views. Nevertheless, checking or un-checking that ‘Allow linking to multiple records’ is a UI device, not actually an element in the definition of the relationship. It simply prevents you from linking a record in table B to more than one record in table A, and vice versa. The same was true above when I was talking about cardinality. Checking or unchecking ‘Allow linking to multiple records’ for a linked column in a given table does not really control whether you can link to multiple records any and everywhere, it just controls whether it can be done in your Airtable views from that point forward. It’s a practical UI control, not a mathematical formula.

I say this is just a UI device mainly because we simply don’t have direct access to the relationships; after all, we don’t even have access to the key fields. But Airtable will give you hints about what’s going on behind the scenes, if you pay attention. For one thing, if you start out allowing table INVOICES to link to multiple items in LINEITEMS, and then you change your mind and un-check that option, you won’t immediately lose those multiple linked items that were linked earlier. To a FileMaker user, anyway, it will soon start to seem that, behind the scenes in Airtable, everything is potentially a many-to-many relationship.

To see this demonstrated, create a simply library database with three (just three) tables: GENRES, AUTHORS and TITLES. On each view, configure the linked columns to allow linking to multiple records. Now populate your tables with values, with (for example) records for History, Drama, Poetry, Novels in the GENRES table, with Shakespeare, Keats, Tolstoy and James Joyce in the AUTHORS table, and a selection of titles form each author in the TITLES table. Notice that you can link War and Peace both to History and to Novel; you can switch to GENRES and add ‘Richard III’ to the History record there. (You won’t need to add ‘War and Peace’ because it’s already there.) And so on.

I am pretty sure that what’s actually happening is that the joins are created as required. Can’t do that in FileMaker Pro natively, i.e. without using SQL. But you can do it in Airtable without breaking a sweat: You didn’t create a single join table! No worrying about circular relationships, either. Pretty remarkable, actually.

.


One-to-one

Anyway, back to your one-to-one relationship. In Airtable, you’d do this by leaving ‘Allow linking to multiple records’ UN-checked on both sides of the relationship.

But as was suggested already, you might want to ask yourself if it’s necessary. Might not be.

In FileMaker Pro, there are multiple reasons for creating one-to-one relationships: two reasons I am aware of are security and network load. I don’t have any databases in Airtable that are large enough for network load to be an issue. As for security, well, Airtable’s current security model is much more limited than FileMaker’s but seems capable of handling most of the challenges I worry about. If you don’t want certain users to see certain columns, you can easily create views in Airtable hide columns. The bigger problem with Airtable is that you can’t currently do something like give a user access to a subset of columns in a table, allow that user to edit some of those columns and and not others, and prevent the user from deleting records.

.


Apples and Oranges

FileMaker (and other RDBMSes like Access, 4D and many others) provide rich relational tools that make it easy to do certain things that are either impossible to do in Airtable (at this time) or, if not impossible, at least awkward and kludgy. On the other hand, some of the differences between FileMaker and Airtable are just differences.

In the first class (where Airtable might seem awkward or kludgy to an advanced FileMaker user) would be the fact that Airtable can’t presently see data two or more linked tables away. In FileMaker, if ZIP CODES are children of CITIES, CITIES children of COUNTIES, and COUNTIES children of STATES, it’s easy to display the name of the state that is the great-grandparent of a given zip code. In anchor-buoy, the linked field might look like this:

zipcodes_cities_counties_STATES::StateName

You can do it in Airtable! But it’s awkward: You have to use lookups to pipe values from one table to the next, to the next. Another example: Airtable doesn’t directly permit multifactor links between tables, that is, you can’t link table A to table B when column A1 matches column B7 and column A2 matches column B9. You might do it by linking table A to table B using formula columns in both tables, but that will feel kludgy to a FileMaker user.

On the other hand, this lack of easy multifactor links between tables isn’t always a weakness of Airtable: It’s often simply a difference. Airtable relies very heavily on its views and filters, almost more than FileMaker relies on layouts. The goal of a multifactor relationship in FileMaker might (in some circumstancese) fairly easily be achieved in Airtable using a filtered view. That’s part of the reason that right now my single biggest complaint about Airtable is that we cannot yet reorder and/or organize views. I expect that is coming and when it arrives it’s going to be AWESOME.

.


Sorry for the length. (I’m apologizing here to anybody who actually read the whole post. That means you, Mom!) There’s a lot more to talk about but I wanted to take a quick walk around the whole topic. Hope it helps.

Will


#7

Thank you very much - very helpful! The reason I want to keep the 1-1 is that each table will be constructed in dribs and drabs as various components come in. I am starting out with a full list of vendors from the prior year of our farmers’ market, and it will change quite a bit in the coming weeks. At the same time, applications from new and old vendors are trickling in, and it’s a lot easier to add the little stub of application details to the bottom of a separate table instead of locating the main record and then adding onto it.

We’ll see, though… it might ultimately be best to paste them all together.

Thanks again.


#8

Not your mom, but I did read it all with great interest! Thanks very much.

I’m coming from lots of different databases that go back as far as mainframes at work at things like Paradox, MS Access on the desktop. It would be really nice if I could wipe my brain clean and start with Airtable and just see what happened! I’m having a blast with it so far!

A couple of mysteries are now cleared up for me, including the whole “Name” thing, the stealth primary keys, and setting of the links in each of two tables. Somehow I wasn’t understanding the very basic “set it on both sides.” I’ll play around with your genres-authors-titles example (how did you know I was a librarian :smile:)

You’ve been most helpful - thanks again!


#9

Wonderful synopsis. Thanks for posting this!


#10

@Martha_Creedon - Check out the search capabilities of Airtable. It may be easy enough to find a record that perhaps an 1:1 relationship isn’t needed.

If not, then we may want to put in a feature request to have the web app filter the records instead of just highlighting them and jumping you to them with the arrows in the search box.

I try to avoid 1:1 relationships as a rule, but sometimes there’s an edge case that makes them necessary.


#11

Check out the search capabilities of Airtable. It may be easy enough to find a record that perhaps an 1:1 relationship isn’t needed.

If not, then we may want to put in a feature request to have the web app filter the records instead of just highlighting them and jumping you to them with the arrows in the search box.

Mike,

Airtable has TWO totally different ways to ‘find’ records:

  1. filter (kind of like a standard FileMaker ‘find’ that modifies the found set
  2. find (kind of like FileMaker’s ‘find next’ function which simply highlights the find term in the next record)

What is it you think Airtable is missing?

Will


#12

Will,

What I’d like to see is the kind of universal search that they have in the iOS app. Basically the type-ahead filter without forcing you to pick the column to filter.

Thanks,
Mike E.


#13

Hello Mike, this is available via the “find” function (#2 in Will’s response). You can activate the “find” function by pressing ctrl+f (cmd+f on Macs).


#14

That highlights the rows that contain the search criteria, but it doesn’t filter the list like the iOS does (unless I’m doing something wrong). It’s definitely good, but if it filtered the the list, it would be great.


#15

Mike,

Okay, you didn’t mention the iOS app at first, and I only just now (thanks to your post) noticed the difference in the way that its ‘find’ (magnifying glass) feature works. You’re right: In iOS, the magnifying glass acts as what might be called a ‘quick filter’. If you’re familiar with FileMaker, this feature on iOS in Airtable roughly corresponds to the Quick Find feature. I say “roughly corresponds” because in FileMaker, the Quick Find really is a find, it just doesn’t specify the field to look in; while the magnifying-glass find in Airtable seems to work only on the records already displayed as a result of a filter.

So now that I understand what you’re thinking about, I’d say I agree that this would be a nice addition to Airtable in the browser.

Will


#16

Will,

#2. find
This search only will search a single column across the whole table.

If I type in column A (fruits) multi category search term (apples) and then a space and type in column B (meats) multi category search term (beef) the resulting from the search is nothing because “apples beef” is not available under a single column within the records. Say I had a long weekend and remember the beef and apples recipe was incorrect and needed to be checked but couldn’t remember the name. I wanted to do a quick search but am unable. I could create a filter or a new view with a filter, but then it wouldn’t be quick, or intuitive.

The “find” is a single column search across all columns and records within the table, but will not give back results across more than one column.

Sometimes I read these forums and am scared that focus is turning away from a simple user experience trying to do more than a spreadsheet, without the hassle. I know Access can do everything I ever needed, the problem is the learning curve is extreme. Keep up the good work Airtable crew! I am more than willing to give feedback!!


#17

Jason,

Sorry but I’m not sure I understand you. No, that’s not right. I’m pretty sure that I do NOT understand you.

##The two types of search
Let me restate and try to clarify what I said earlier: Airtable has two ways of ‘finding’ values in a table:

  1. Filters (click filter icon and define a filter)
  2. Finds (click magnifying glass icon and enter some find criteria)

A filter modifies the ‘found set’, that is, it hides records that do not satisfy the filter. When a filter is defined, the column is specified and only the specified column is examined. If you define a filter asking for records that have ‘Washington’ in the Name field, you’ll find Larry Washington and Susan Washington-James, but you won’t find anybody who lives in the state of Washington or on Washington Avenue. For filters, column specification matters.

For magnifying-glass finds, the column doesn’t matter. But finds are limited in two other ways. First a find only applies to the records already returned by a filter (if there is a filter). And second, a filter applies only to the columns that are not hidden on the current view. In short, a find is only applied to what was already on display before you did the find.

Examples

Imagine a table named ‘Animals’ with a total of five rows (records). Because the forum will only let me insert one screenshot, I’ll try to mimic Airtable’s display. I’m using a bullet character • to indicate the break between one column and the next.

_ • NAME • NOTES
1 • Pig • smarter than it looks
2 • Cat • doesn’t like dogs
3 • Dog • man’s best friend
4 • Aardvark • cute!
5 • Coyote • similar to a dog

Now if I define a filter (NOTES contains ‘Dog’), I get this result:
_ • NAME • NOTES
1 • Cat • usually doesn’t like dogs
2 • Coyote • similar to a dog

This doesn’t include the actual record for Dog because the filter looked only in the Notes column.

If I deleted the filter and was once again looking at all five rows, and if I then clicked on the magnifying glass icon and typed ‘dog’, I’d see something like this:

_ • NAME • NOTES
1 • Pig • smarter than it looks
2 • Cat • doesn’t like dogs
3 • Dog • man’s best friend
4 • Aardvark • cute!
5 • Coyote • similar to a dog

Where I’ve applied boldface, Airtable would highlight every occurrence of the string “dog” in the five records that were originally found (i.e. filtered). It does so in multiple columns — values are highlighted both in the Name (for Dog) column and the Notes column (for Cat and Coyote). (Incidentally note that Airtable highlights not just the matching string but the entire cell.)

What about a filter and a find together? In that case, the filter goes first, and then the find does its thing. For example:

_ • NAME • NOTES
1 • Cat • doesn’t like dogs
2 • Coyote • similar to a dog

Here, I had defined a filter asking Airtable to display only records that have “dog” in the Notes column. And then I added a magnifying glass find for “dog”. This highlights “dog” wherever it occurs but only in the two records found by the filter. The find does NOT override the filter and display the record whose name = “Dog”.

Now, say I leave the string ‘dog’ in the magnifying glass’s find-what field, but change the filter so it asks for rows whose Name column contains the letter “o”. Now I’ll be looking at this result:

_ • NAME • NOTES
1 • Dog • man’s best friend!
2 • Coyote • similar to a dog

Here again, the filter is applied first, and then the find.

##Bravo, Airtable!
Both of these ways of searching are valuable, the ability to use them together is important — and yet it’s not really complicated at all, after a little practice. This isn’t especially innovative but I think Airtable’s developers have done a terrific job of making these tools work in your browser.

##And on the iPhone?
Okay, things do work a little differently on the iPhone. I’m not completely thrilled about that, although I think I know why Airtable’s developers have allowed this difference of behavior.

On the iPhone, the magnifying-glass find is basically the same as on the computer in your web browser, but on the iPhone, a find also behaves a bit like a quick and dirty filter, in that it modifies the apparent found set. If the filtered set of records originally contained five rows but only two of them match the find string, on the computer you’ll be looking at five records two of which are highlighted, but on the iPhone you’ll be looking at just the two records.

I’d bet five cents that it’s done for UI reasons: so that more records can be viewed on the mobile display’s limited real estate. But, like on the computer, the find on the iPhone only narrows the set returned already by any filter that is active for the view. Here again, the find does not override the filter. Here’s an example from the same base I was using above, but viewed on my iPhone:

The filter previously applied on this view (not visible in the screenshot) shows records that have ‘dog’ in the Notes field. That’s just the records for Cat and Coyote. Now, on top of that, I did a magnifying-glass find for the string ‘dog’. NOTE that we’re still only looking at two records. If there weren’t a filter active on this view, we’d be looking at three records: the records for Cat and Coyote (which have ‘dog’ in their Notes column) and the record for Dog as well.

So even on the iPhone, a magnifying glass find does not search through all records.

Will


#18

Examples

Imagine a table named ‘Meals’ with a total of 4 rows (records) and 4 columns. I’ll try to mimic your mimic. I’m using a bullet character • to indicate the break between one column and the next.

_ • NAME • Fruits • Vegetables • Meats
SouthWestern • Tomatoes • Corn • Chicken
Breakfast Balance • Apples • Broccoli • Egg
Beef Starch Yum! • Tomatoes • Potatoes • Beef
Easy Canned “Chilli” • Tomatoes • Beans • Chicken

If I then clicked on the magnifying glass icon and typed ‘Tomatoes Beef’, I’d see something like this:

0 results

Therefore “The “find” is a single column search across all columns and records within the table, but will not give back results across more than one column.”


#19


#20

Jason,

Great, thanks for replying. Let’s wrestle this to the ground.

First, you’re talking about the iPhone, right? I assume that’s true because in the web browser on the computer, your example doesn’t work as you say. If you typed ‘tomatoes beef’ on the computer, you’d still see the same records you were looking at initially. Just nothing would be highlighted.

Second, I simply don’t understand your comment that the magnifying glass finds “across all records within the table.” The way I understand your words, that’s just not true. Even on the iPhone, the scope of a magnifying glass find is the set of records returned by the active filter. If you had another column in your example that would indicate whether the meal was Hot or Cold, and you’d filtered to show only Hot meals, if “tomatoes beef” actually happened to occur in a column in one of the Cold meal records, it will wouldn’t be returned, because, as I said, the filter limits the scope of the find. So finds do not look “across all records in the table.” Not even on the iPhone.

Third, I don’t mean to quibble, but I think saying the find is a “single column search across all columns” is unclear if not downright misleading. I’d prefer to say that the value you type into the find-what field is treated as a string. Airtable will look in every column in the current view of the filtered records, but it’s only going to find exact and complete matches for the string you typed. If I modified my own example base so that the note for Coyote read “bit like a dog” (and Cat’s note still read “doesn’t like dogs”) and I asked Airtable to find “like dog”, on the computer it would highlight only the notes cell in the Cat records. It wouldn’t flag Coyote because “like a dog” does not contain the string “like dog”. That’s just how string searches work. If you want to look for xyz in column 1 and bcd in column 3, use a filter. Works same way on iPhone.

Will