Table inheritance

Ask for Help
1

Hello! Could use some input on how to best construct a schema here, having some trouble conceptualizing how it’d look in Grist.

Short version

I have a use case for polymorphic relations, which I’d like to solve with class table inheritance. But I don’t see any way to create SQL-style triggers, so that’d require double inserts, which I’d like to avoid. Any ideas or suggestions on alternatives, or on methods to ease the double insertion?

Long version

Simplified example: I have a database of entities of varying types, such as: part, assembly, container, location. Each type has a set of attributes unique to that type, along with a set of common attributes that are shared among all types. So, one table per entity type.

One such common attribute is placement, which indicates where an entity is physically present. In my tables, this is a parent field that references the same entities table, along with its two-way counterpart children. Each entity can have only one parent, and placement must be acyclic. Thus, entity placement can be shown as a tree. Great!

parts (
  id PRIMARY KEY,
  parent REFERENCES assemblies,
  example_part_attribute,
  . . .
)

assemblies (
  id PRIMARY KEY,
  parent REFERENCES containers,
  example_assembly_attribute,
  . . .
)

First problem: while placement has a hierarchy, rungs can be skipped or repeated; all these configurations are valid:

location > container > assembly > part
location > assembly
assembly
container > container > part

My parent column can’t reference multiple tables. But, since rungs can be skipped or repeated, I can’t have a separate parent field for each type (e.g. parent_assembly). So I combine all my disparate type tables into one large entities single-inheritance table:

entities (
  id PRIMARY KEY,
  type ENUM('part', 'assembly', 'location', 'container'),
  parent REFERENCES entities,
  example_part_attribute,
  example_assembly_attribute,
  . . .
)

Second problem: This gets cumbersome quickly; some types have many unique attributes, so my one giant table end up with lots of columns and nulls. Worse, many formulas are specific to entity type, so every formula would need to first validate the type and split to sub-functions. Dropdown conditionals helps with some of this, but not most.

Class table inheritance solves this, where one entities table holds all the common fields (including parent), and then each type gets its own subtable with all the extended fields, with one row for each corresponding row of that type in entities:

entities (
  id PRIMARY KEY,
  type ENUM('item', 'assembly', 'location', 'container'),
  parent REFERENCES entities,
  . . .
)

parts (
  id PRIMARY KEY REFERENCES entities,
  example_part_attribute,
  . . .
)

assemblies (
  id PRIMARY KEY REFERENCES entities,
  example_assembly_attribute,
  . . .
)

Third problem: This works great, except now adding a new entity requires an insertion in both the entities table and the entity’s type subtable, which is also cumbersome. Maybe I’m missing it, but I couldn’t find any way to automatically TRIGGER insertion of a record in a table when a record is created in another table. Is such a feature possible?

I also couldn’t find a satisfying way to make the UX of double insertion feel smoother, but I may well have missed a good method. Any ideas for making the data entry smoother, or a better way to approach this entirely? I did find this previous question which is related, but not quite right. Apologies if I missed something else.

Thank you so much for the time and help!

2

There is an undocumented method lookupOrAddDerived, which behaves like lookupOne but creates a matching record if one isn’t found. You could use it to trigger the creation of the second record – you could add a column to the Entities table with a formula like this:

if type == "parts":
  Parts.lookupOrAddDerived(entity=$id)
elif type == "assembly":
  Assemblies.lookupOrAddDerived(entity=$id)
...

In terms of usability of the end result, I don’t know if that would be better than one (bloated) table, but maybe.

In terms of using lookupOrAddDerived, the reason it’s not documented is that it’s buggy if used beyond simple one-way cases like this, and its limitations might be fundamental. But I expect it would work for this example.

1 Like
3

You may want to look at Sqlite triggers. Yes, this is not pure Grist, but I can tell you it works as Grist is Sqlite-based. If you know a little bit of Python, it is not that hard to populate an existing Grist document with Triggers. There are plenty examples on the internet. However, it may lead to conflits if not used carefully. Feedback from the technical team on such approach would be interesting.

One last advice if you take this path: during your try’n’learn path, be sure to clean-up all existing triggers before adding new, to avoid accumulating them…