Question about base design and many-to-many relationships

[Gavin_Beinart-S]

Hi all. I’m a PhD student in history using Airtable to manage archival research for my dissertation. My research involves tracking individuals and family groupings through many different archives.

At the moment, I have a table for “people” and I will eventually have multiple tabs for the different archives I do research in. The “people” table is my primary table, and then I link those people to specific archival records in the archives tables. The first goal is to be able to see at a glance and analyze all of the different archival records across multiple tabs that relate to that individual.

The issue of course is that many archival records have more than one person represented, and every person has multiple archival records. So, I need to represent “many-to-many” relationships. For example, I have a collection of letters in which I want to show who the letter is from and who it is to. I’m wondering if I’ve designed this right, if I need any kind of junction table, and if there is a better way of doing this.

Additionally, I am wondering what is the best way to connect up families in my “people” table. I could organize by surname, but obviously surnames repeat between families. So it would be helpful to create a uid for each family. Should I make a separate “family” table and then add in all the members from the “people” table? Should I then link the respective family from the “family” table to the archival tables as well as each individual from the “people” table?

The purpose of this, and my second goal, is to be able to see not only all of the archival documents that relate to a particular individual (as described above), but also all of the archival documents that relate to a particular family, and to see how the members of that family are interacting through those archival documents (letters or otherwise) across different archives and over time.

Any suggestions would be greatly appreciated!

[Databaser]

Gavin_Beinart-Smolla:

and I will eventually have multiple tabs for the different archives I do research in.

HI @Gavin_Beinart-Smolla and welcome to the community!

Just a question, but does every archive has to have its own table? I don’t know if the fields will be the same for every table, but couldn’t you work with a field that indicates the archive?

[Gavin_Beinart-S]

Thank you!!

I think it would make sense for each archive to have its own table. Firstly the fields would be somewhat different for each archive, and secondly I think it will be less unwieldy for me to manage it that way. I also want to be able to perform analyses within each archive/collection.

But I am very open to suggestions - what do you think the benefits would be of condensing all the archival finds into one table? Would it make a difference in terms of the linked data?

[Justin_Barrett]

I agree with @Databaser about putting all archives into a single table, and using either a single select field or a link to another table to identify the specific archive that each record belongs to. I tend to prefer links to other tables when there’s more data that I want to track about a given related item besides just its name.

Gavin_Beinart-Smolla:

what do you think the benefits would be of condensing all the archival finds into one table? Would it make a difference in terms of the linked data?

In terms of benefits, it would make it a lot easier to track certain things, in part because (to answer your second question) it would simplify the linked data. For example, if a person needed to be linked to more than one archive, that would require separate link fields in the [People] table if each archive were on its own table. Tracking how many archives each person links to would require a separate formula field to collate the data from all of those link fields. With one table for all archives, you only need one link field from [People], and you could easily count how many archives a given person is linked to using a single rollup field.

[Gavin_Beinart-S]

Hi Justin - thanks so much for this detailed reply. Given the significant variety in the types of fields that I need for different archives, I think that I could probably divide the archival material into 2-3 types and therefore 2-3 tables. That would presumably still be a lot easier to manage than the dozens of tables involved in my original plan. For me counting how many archives someone appears in is less the issue - the main goal is to see at a glance all of the places one person is mentioned, and preferably to use a date field to put them all in chronological order. Which it seems like I could do with a couple of rollup fields, right?

Do you have any suggestions as to how to connect family members? I was thinking in my “People” tab I could have different columns for ‘mother,’ ‘father’ and ‘sibling’ and use linked records within the same table to link the families together. What do you think?

Thanks for your help!

[Justin_Barrett]

Gavin_Beinart-Smolla:

Given the significant variety in the types of fields that I need for different archives, I think that I could probably divide the archival material into 2-3 types and therefore 2-3 tables. That would presumably still be a lot easier to manage than the dozens of tables involved in my original plan.

Possibly, though I would still consider a single table if there’s a reasonable amount of similarity between archives. You could use different views to show specific combinations of fields for different archive types, which would still simplify the way that links would work to the [People] table.

Gavin_Beinart-Smolla:

For me counting how many archives someone appears in is less the issue - the main goal is to see at a glance all of the places one person is mentioned, and preferably to use a date field to put them all in chronological order. Which it seems like I could do with a couple of rollup fields, right?

Not necessarily. Rollups are good for aggregating data points from various linked records, but there currently aren’t any options for sorting that data in the rollup field’s output. The order of items in the array that the rollup receives is determined by the order of the links in the related link field. If the links are made in chronological order (based on a date field, for example), then you’re good to go. (Link order can be changed after the fact, but the only reference you have is info from the primary field of the linked record.) However, you’re talking about combining things across several rollup fields (guessing from the several archive tables) in which case you’re unfortunately stuck. There are array functions that let you combine multiple arrays—e.g. the unprocessed values output from multiple rollup fields—but that just concatenates the arrays end to end to make a single array. There are still no functions for sorting arrays; even if there were, it would still be based purely on the text of the rolled-up target field.

For controlled sorting of data, you’ll have to look at more robust reporting tools. Scripting could help because scripts can sort on virtually anything, but that would just give you the raw sorted data. Presenting that data in a useful and pleasing way is another challenge, and unfortunately I don’t have a ton of experience with reporting tools. I’ll have to let others chime in on that topic.

Gavin_Beinart-Smolla:

Do you have any suggestions as to how to connect family members? I was thinking in my “People” tab I could have different columns for ‘mother,’ ‘father’ and ‘sibling’ and use linked records within the same table to link the families together. What do you think?

That would technically work, but the thing to know about same-table links is that Airtable doesn’t automatically create reciprocal links like it does for cross-table link fields. So while you could make a {Father} link field that lets you link a child to his/her father, you wouldn’t automatically see that child linked from the father’s record. You’d have to manually go to their {Children} link field to make those links.

An alternate way to approach it would be to create a pseudo-junction table named [Families]. I’ve gone back and forth while writing this reply trying to think of various ways that this could be designed, and there are pros and cons of all the options. One is to have specific role fields that link to specific people; e.g. {Father}, {Mother}, {Children}, etc. The downside is that this just makes more link fields to deal with on the person’s individual record. The other option is to just link everyone from {People} to their respective family via a single target link field, but that wouldn’t allow you to specify parents vs children.

FWIW, if you’re doing genealogy work and are looking to track that kind of stuff, there are already very robust and dedicated tools out there for just this purpose. They’re designed from the ground up for tracking people, connecting them to the various places where they’re mentioned in historical documents, etc. No sense in trying to reinvent the wheel when these types of tools already exist.

[Gavin_Beinart-S]

Justin_Barrett:

Not necessarily. Rollups are good for aggregating data points from various linked records, but there currently aren’t any options for sorting that data in the rollup field’s output. The order of items in the array that the rollup receives is determined by the order of the links in the related link field. If the links are made in chronological order (based on a date field, for example), then you’re good to go. (Link order can be changed after the fact, but the only reference you have is info from the primary field of the linked record.) However, you’re talking about combining things across several rollup fields (guessing from the several archive tables) in which case you’re unfortunately stuck. There are array functions that let you combine multiple arrays—e.g. the unprocessed values output from multiple rollup fields—but that just concatenates the arrays end to end to make a single array. There are still no functions for sorting arrays; even if there were, it would still be based purely on the text of the rolled-up target field.

I see what you mean. I have a somewhat inelegant but still workable solution: in my archives table I have a “people” column where I link all the people from the “people” table mentioned in that particular archival document. In the archives table, I can group by the “people” column and then sort by the “date” column. That gives me a breakdown of all the different interactions between people sorted chronologically. Not exactly the same as what I described before, but still extremely useful for my analysis. And I will keep thinking about how I can use rollup fields to my benefit - thank you for this rundown of their limitations.

Justin_Barrett:

FWIW, if you’re doing genealogy work and are looking to track that kind of stuff, there are already very robust and dedicated tools out there for just this purpose. They’re designed from the ground up for tracking people, connecting them to the various places where they’re mentioned in historical documents, etc. No sense in trying to reinvent the wheel when these types of tools already exist.

Yes, you’re right about this. In Airtable, I think it will suffice just to have a “surname” column in my people table and group by that. That would work fine as long as I don’t have any unrelated families with the same surname, which I hopefully won’t because it’s too confusing anyway. And then if I want to do any kind of genealogical analysis I can use genealogy software.

Thanks again for all of your advice!