Polymorphism would be nice: a way to join tables with common columns under a parent or generalized table

Suggestion Box
2

For completeness, below is a survey of existing approaches and workarounds.

Currently there are four methods that I know of. I will illustrate them with the domain explained in the “Use cases” section in the original post.

  1. Create an Events table with the common Start, End, and Name columns. Create three views of that table, named Meetings, Classes, and Exams. Each view adds new columns for all of the properties it defines, and shows only the common columns and its relevant columns.
    The downsides are that this potentially creates a large number of unused columns (for example, the Exams view will have all the columns from the Meetings and Classes table, but they will all be hidden and empty). It also creates namespace issues, if two of the views have similar columns with slight differences (i.e. different formula or settings), then they will need to use different names.

  2. Create an Events table with the common Start, End, and Name columns. Create three new tables named Meetings, Classes, and Exams. Each new table has only the columns for all of the new properties it defines, plus an additional lookup column that references a row in the Events table, which contains all the event-related information for that row.
    The downside are that the data is no longer flat, and the event-related information cannot be directly embedded inside the tables. Also, creating a new row requires creating a new row in both the specific table (e.g. Meetings) and a new row in the Events table, and then connecting them. You end up with a large table of Events and no good way to link each row to a row in one of the specific tables (unless you create three different columns in the Events table to lookup the rows in the three different specific tables). Some of these can be solved by using automations that run upon creating new rows or buttons to create rows in both tables and link them.

  1. Similar to 2, but delay the creation of the linked events to the viewing stage with manual processing or automations. Create an Events table with the common Start, End, and Name columns. Create three new tables named Meetings, Classes, and Exams. Each new table has only the columns for all of the new properties it defines, including the event-related information such as Start, End, and Name. When it comes time to join the rows of the Meetings, Classes, and Exams tables as events, simply use a button or an automation to process all the rows from each table and create a corresponding row in the Events table that copies the event-related info from the appropriate columns in each table, joining a copy of the rows into the common Events table.
    This is not ideal but might be sufficient for many use cases as it does not alter the representation of the data in the specific tables, but it does require manually joining the rows (which can be automated using automations).

  2. Similar to 2, but push the handling of common information onto an external integration. In our use case, you might use the Google Calendar pack to create events for each row in the specific tables, and then have a sync table that pulls in all of the Google Calendar events. This has much of the same drawbacks (info no longer embedded, and need to create the new row and create a new Google Calendar event).

2 Likes