Re: Primary key and foreign key
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.)
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_face:
.
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
