How to model multi-level hierarchy for biospecimen inventory

[achamess]

I’m a researcher and I deal with biospecimens. One of the major tasks is to process and store specimens. A standard arrangement is to place samples in a storage for long-term keeping. I’ve drawn out what that looks like. Both the samples and the storages have multiple hierarchies. How would one model this in Airtable?

[Bill_French]

achamess:

How would one model this in Airtable?

Great diagram!

So, the short answer is lots of tables, links, and lookups. But a deeper question nudges me to ask - how does the data representing these specimens arrive in the digital world? Are they hand-typed into Airtable tables? Or, are they flowing toward Airtable from another system or data entry pathway?

[achamess]

Thanks @Bill.French

Yeah, that’s what I thought. I love the customizabilty of Airtable, but the cognitive overhead to create this kind of setup is daunting. Which is why commercial offerings probably can charge so much.

As to the input (how do things get into Airtable), I’m not sure yet. Would depend on how challenging it is to make this kind of hierarchical setup work. but ideally the input would be entirely in Airtable, with manual data entry and possibly barcoding.

[Bill_French]

achamess:

ideally the input would be entirely in Airtable, with manual data entry

Which suggests users would have establish all the relationships; not a pretty picture if such relationships are complex and it appears they may be.

So two ideas come to mind:

1. You are on the verge of describing a graph, not a relational database. I’m sure some of the SQL purists will disagree and maybe this is a lot simpler than I fear. But, I see a graph pattern and if not based in the data model, certainly vast benefits to querying scientific models like this with something like GraphQL.

2. Automation that imposes an ontology given the raw data. It’s possible to separate the relational model into a series of automated filters that instinctively create all the necessary links from raw data. This could be done in script automations or script blocks.

Recommendation …

Using relational link fields attempt to model one part of the diagram - like start with Samples (2) to Box C - that should give you a sense of the implementation that would depend on users to manually establish the relationships.

You might also want to then surface that into a form to see if users could feel comfortable selecting lookups into other tables.

[achamess]

Thanks for the info @Bill.French

I like idea of a graph database, but then Airtable would not be the right tool. People can and do use relational databases for this, as there are many commercial offerings based that I think are MySQL on the backend.

In any case, I’ll try messing around a little more, but I may need to just give up the idea that Airtable can handle this. I’ve looked on this site to see if there are other biomedical researchers using Airtable for all their needs and it doesn’t look like it…

I’ll report back if I can get this to work for me.