The desired functionality is to somehow have an umbrella table that would join together (in the SQL sense, i.e. to merge the rows of multiple tables on some common set of columns) multiple otherwise unrelated tables. The intention being to have a way to:
- Aggregate all rows from multiple tables into a view that presents the row values for the common set of columns.
- Considering this view of the rows as a more abstract or generalized table, to reference this table within formulas and lookups.
This has been previously discussed here:
wherein @Federico_Stefanato proposes to extend an initial parent table with subtables “that only define the differences needed to be tracked.” (see the “Proposed approaches” section below for a review of this approach).
My approach is quite similar, but is more bottom-up and data-centric. Rather than create tables to be extended (which implicitly creates the concepts of table type and inheritance), I would create a table that joins together the rows of other specified tables. This more abstract parent table would have only the common columns among all of the specified or “child” tables, and would act as a view of the rows of the specified tables projected on those common columns. You could change the set of common columns by changing the columns on one of the child tables, or adding a new column to each child table.
The table itself would be read-only; adding new rows directly to this parent table, modifying columns types and settings, and modifying existing rows would all be impossible.
Under the hood, this would simply be a unidirectionally synced join of the specified tables on the common columns (would need to determine the intersection of the columns and column types of all specified tables).
The UX would be to leverage the same table creation menu, but simply allow selecting multiple existing tables (with the option to change this selection later on). This would create a new multi-view (or some other catchy name) of those specified tables, that can be referenced in formulas and behaves like any other table (with the exception of being read-only in all contexts.)
There is a good use case explained in the discussion linked previously.
Mine specifically was to consider rows in multiple unrelated tables as events. For example, I had a
Exams tables. All of them had a
Start date time, an
End date time, a
Name, as they are each collections of types of events with their own unique concerns and additional columns. I wanted to render these tables in a unified calendar.
Federico’s approach proposes a more top-down way (similar to many OOP programming languages out there) of doing things, where a general base or parent table (type) is declared and then more specific sub tables (types) are declared that extend it. IMO, I find it unnecessary to pre-emptively declare the extension relationship between tables at creation time. What happens if you want to make an existing table extend another one? (even if you add an alternative UI to accomplish this, the semantics of making an existing table extend another is unclear to non-programmers.) I also find it goes against most of the design flows in Coda, where data is first created then processed (for example, having to create a table type for a lookup column and then create the table to be looked up would also feel weird). Ultimately, both approaches are essentially two sides of the same coin, and differ mostly in how they are framed upon creating the relationships.
This is likely the biggest issue as there is no clear decision that intuitively follows from the design. I think naively columns should only be considered common if they have the same name and exactly the same type in all specified child tables. There could be some leeway for example in different types of dates or numbers being coerced to the most general type.
There is also the concern that columns on the parent table cannot be directly edited (adding a formula or changing a setting) as that would mean that there would be columns that are imposed onto the child tables (would need to be only editable on the parent table). IMO this is also too complex so I would not include it.
It should be impossible to add new rows directly to the parent table, as this introduces more complexity (the parent table becomes a concrete type that can have its own rows, as opposed to only show rows from the specified child tables, which comes with a whole new set of decisions). Also it can easily be accomplished by creating another table, with the same shape as the parent table (same columns and column types) and then simply making it another specified child of the parent table.
It might be possible to add columns to the parent table, that are automatically added to the children tables. Settings on the parent table column are simply copied over into the new column in the child tables, but can be replaced individually in each child table. Ultimately this would just be a shortcut to adding the column directly to each child table, and introduces other complications, so it is not necessary.
There would be a simple way to distinguish the joined rows by simply having a
Type column common to each child table, that would be a select list with the names of each child tables as options (and an appropriate value for new rows). There could be an argument for having this column be automatically created and managed in the parent table.
There should be some way to navigate to the full row in the specified child table from the row in the parent table (not in formulas, but through the UI). Could be as simple as a link in the full screen view of that rows like “See full row in [Child table name] table”.