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!