Managing Relational Content with Airtable
I was recently tasked with building a solution for managing a taxonomy of best practices. The best practices were categorized by group, collection and industry, and they were meant to be used as part of a rubric for evaluating companies. This article explains how Airtable can be used to create a powerful tool to complement the process of reviewing and updating this content on a regular basis.
The Problem
To start, let’s define a few terms to make it easier to understand the context, and describe the problem and the solution:
- Best practice: A recommended practice for businesses defined by its description and/or the way it has been categorized. For example: “Have the system certified to a recognized local or international standard.”.
- Group: One or more best practices combined around a topic. For example: “Management System Rigor”.
- Collection: The name of a business operational division that combines one or more groups of best practices. For example: “Governance & Management”.
- Industry: The name of a distinct group of productive or profit-making enterprises. For example: “Construction”.
- Content: All the best practices, groups, collections and industries, and the relationships defined between them.
- Content Editor: Person responsible for compiling and managing the content.
Here are some more details on the content’s relationships:
- A best practice belongs to only one group.
- A group may be included in more than one collection.
- An industry is characterized by a set of collections.
- Each collection may belong to more than one industry.
Next, a description of how the content is used and managed:
- The content is exported and fed into a database to power some tools/applications.
- Every few months, a committee of industry experts is asked to review and provide feedback on the list of best practices and the taxonomy created for its industry.
- The content should be readily available for all members of the industry committee to review.
- It’s important to have a way to gather feedback from each industry expert and help the industry committee reach consensus.
- After the industry committee has reached consensus on any changes to the content or its taxonomy, the content editor proceeds to make the corresponding updates.
- After all updates have been made, we go back to step 1 and the process starts all over again.
The Solution
I was asked to build an app for this, but my first thought was: “wouldn’t a spreadsheet be enough for this?”. However, I quickly realized that a Google Spreadsheet wouldn’t cut it because:
- It would be difficult or hacky to manage 1-to-N and N-to-N relationships in the content.
- There is currently a total of 25–30 industries. Asking industry experts to open a massive spreadsheet, and provide feedback on the best practices and the taxonomy that pertain only to their specific industry does not seem like an effective way to gather feedback.
- The size of this spreadsheet and the number of people interacting would likely require time and effort detailing a process to keep the data organized in order to help the industry committee reach consensus on the feedback and be clear enough for the content editor to make the updates.
After experimenting with some of Airtable’s features, I decided to flesh out a solution.
Overview
Airtable is a tool that combines the power of a database with a user interface similar to a spreadsheet, providing users the ability to create solutions and apps around their data. Airtable’s linked record fields, reporting capabilities, and ability to collect information from external users made it a suitable option to manage this taxonomy of best practices.
Within an Airtable base, the solution would include 5 tables: Best Practices, Groups, Collections, Industries and Feedback. The first four tables would be dedicated to managing the content and the last one would be used specifically by the industry committees to gather and sift through feedback. To start, we’ll focus on the content and then, we’ll add the ability to collect feedback.
Initialize the Content
To create the content tables, we’ll first import a CSV file with the data for all the best practices. The CSV file will have the following columns:
- Name: Name of the best practice.
- Groups: Name of the group that the best practice belongs to.
- Collections: Comma separated list with the name of all the collections that include the best practice. If a collection name has commas, it will need to be wrapped in double quotation marks.
- Industries: Comma separated list with the name of all the industries where the best practice applies. If an industry name has commas, it will need to be wrapped in double quotation marks.
Once we have the CSV file, we can switch to Airtable and create a new base with this data.
The import should result in the creation of a new base with one table.
We’ll rename the table to Best Practices. Next, we’ll change the field type for the Groups, Collections and Industries columns from “Single line text” to “Link to another record”.
For each one, we’ll create a new table with the name of the column.
Because a best practice will belong to only one group, the switch that reads “Allow linking to multiple records” can be turned off when creating the Groups table. When creating the Collections and Industries tables, the switch must be turned on. When asked to add lookup fields, select Skip for all three tables.
In the end, you should have all four content tables populated with the content from the CSV file. It’s important to notice that by linking the groups, collections and industries values from the Best Practices table to records in the other tables, we are normalizing the data; thus, making the content editor’s life easier.
Collect Feedback
Once all the content is in place, we’ll move on to the next phase which is the ability to collect feedback from industry experts. Industry experts are asked to provide feedback related to the taxonomy, completeness and correctness of best practices in their industry. To streamline this process, we’ll need to provide them with a view of the data that is relevant to them and a form to collect all their observations/suggestions related to it.
Create a Custom View
To create a custom view for the industry experts to view the best practices data, we’ll select the Best Practices table and create a new grid view named Best Practices Report.
The Best Practices Report will categorize best practices by industry, collection and group. This will help industry experts visualize the structure of the taxonomy for their industry.
After grouping the best practices, it doesn’t seem necessary to display the group, collection and industry fields anymore so these can be hidden. Additionally, we can sort the best practices by name.
Create a Feedback Form
Industry experts will want to provide feedback after viewing the Best Practices Report; however, we don’t want them to directly change any of the content. Instead, we want to gather all their feedback so it can be discussed and voted on by the industry committee. To do this we’ll create a new empty table named Feedback that will be fed data from a feedback form.
The table will have a text field to store users’ feedback, a field to link to the user’s industry and a field to link the feedback to a specific best practice. The table may also include fields to link the feedback to a collection and/or group. All these changes can be made by selecting the proper field types i.e. long text for the feedback field and linked records for the others.
After creating the Feedback table, we can now let external users add content to it via forms.
We’ll create a new form named Feedback Form. By default, all table fields will appear in the form. We’ll include a description for the form and sort the fields in the order that they should be filled in by users. Finally, we’ll enable the following submit options (at the bottom of the form) “Show a ‘another submit response’ button” and “Show a new blank form after 5 seconds” so users are encouraged to submit more than one feedback form and keep feedback concise and specific to a particular topic or best practice.
Putting It Together
Airtable provides us with a link to share the feedback form and it also lets us share the Best Practices Report; however, we don’t want users to have to switch back and forth between two separate tabs in their browser. Instead of sharing separate links, we’ll embed them both in an HTML page so only one link is shared and users are able to view the report and submit feedback at the same time.
In the Best Practices table, we’ll select the Best Practices Report. Then, we’ll select the Share View option and choose Create a shareable grid view link. Finally, we’ll select the option Embed this view on your site to get the code snippet that will let us embed the view in an HTML page.
Copying the code snippet to embed the feedback form in an HTML page is very similar to the steps described for the Best Practices Report. Using HTML5 Boilerplate and some basic HTML and CSS, we can place the grid view and feedback form next to each other in an HTML page that works across browsers.
Instructions for how to deploy and serve this HTML page is outside the scope of this article; however, I was able to do it with Vercel in a few minutes.
Closing the Loop
After the content and the feedback form have been shared with the industry experts, feedback will start coming in. Feedback will go through a review process where the industry committee will need to review and organize the feedback before deciding on how/what content will be updated. Finally, the content editor will proceed to make all the changes agreed upon by the industry committee. By adding a status field and a kanban view to the Feedback table, we can ensure this process becomes clear and agile.
First, we’ll add a “single select” field to the Feedback table named Status, and we’ll make available the following options:
- Discard: Feedback that the industry committee has decided to ignore.
- Duplicate: Feedback that appears duplicated in another record.
- Accept: Feedback that the industry committee has accepted and will need to be updated by the content editor.
- Updated: Feedback that the content editor has made and can be seen reflected in the content.
To complement the status field, we’ll add a kanban view to help the industry committee and the content editor organize and keep track of all the feedback. With the Feedback table tab active, we’ll create a new kanban view and use the status field as the grouping field.
By default, any new feedback will not have a status value set so it will be up to the industry committee to review and classify the feedback into one of the three stacks: Discard, Duplicate or Accept. Moreover, if one feedback record covers several topics, it may be a good idea for the industry committee to break up the feedback into separate records so that they can be voted on separately. Using the kanban view, the industry committee is able to productively drive the discussion and act on any submitted feedback.
The process seamlessly transfers to the content editor. Any feedback in the Accept stack has the green light from the industry committee to be merged into the content. This means the content editor can proceed to update the content with any items in the Accept stack. Every time he updates the content, he is responsible for moving the corresponding feedback records from the Accept stack to the Updated stack. This way, the industry committee and the content editor have visibility of all the feedback and their status at all times.
Although it’s expected for the content to be updated only once a year, the transparency of this process could potentially reduce the cadence.
Final Thoughts
The solution presented with Airtable meets all the requirements related to the creation and maintenance of a taxonomy of best practices. The solution allows users and stakeholders to access and contribute content easily. In contrast to a spreadsheet, Airtable allows relationships between best practices and other entities to be defined. Consequently, the definition of relationships via linked records also results in normalized data that makes it possible to maintain all entities (best practices, groups, collections and industries) at minimal cost.
In the solution, only the relationships between best practices and the other entities have been established in the tables. The relationships between groups, collections and industries were not defined because they did not bear any obvious significance to the final solution. However, they can be accomplished by adding additional linked record fields to those entities. Alternatively, it is still possible to get information on the relationships with the current solution by adding more grid views to the Best Practices table and grouping by a specific entity.
Though this solution seems to satisfy the requirements reasonably well, the key ingredient to its success will be user satisfaction. One of the main advantages of this solution is that it very closely resembles a software interface that most — if not all — users are familiar with: a spreadsheet. This should help reduce friction and incentivize adoption from the stakeholders: the industry committee and the content editor. Industry experts, on the other hand, would experience a different interface that may require a brief introduction. In all cases, it would seem important to follow up the use of this solution with a short survey to measure user satisfaction and discover opportunities for improvement.
As for cost, this solution could easily be implemented and put in production in half a day — assuming that all data in the imported CSV file is in the correct format. Since this solution has beed designed around content in the public domain, it may be possible to implement it using Airtable’s free tier. However, it would be necessary to upgrade to a plus account if the limit of records per base in the free tier exceeds 1,200. Furthermore, if the content were not in the public domain or if it were necessary to impose strict editing permissions or lock views, then it would be necessary to upgrade to a pro account.
Given the ease of implementation and potential benefits to users, this tool would be well worth its cost. Give it a try. Let me know how it worked for you or any other tips you would like to share. Thanks for reading!
