New to Airtable - some questions

You should store the child information in another table, and relate them with the Cases table with a Linked Record field: https://support.airtable.com/hc/en-us/articles/206452848-Linked-record-fields

Thank you for the reply!

I think I understand what you are suggesting. One question for clarification: I can’t just link to, say, Client_Name since that is a single reference (parent > child) & would ignore the rest of the children in that table. I should create a Case_ID field (as an example) that carries through all the tables so each child links to the Case_ID? Is that how this is best accomplished?

Thanks again.

I don’t understand your question. Just create the 2 tables and use the Linked Record field.

1. You link to the record, not to any field
2. When linking, you see the primary field, and the closest fields to that
3. In the main table, you’ll see the linked record by the primary field

Try it and come back, it’s easier than you think. You can check the help article too.

Essentially, your question concerns datamodeling — that is, how best to structure, organize, and access information stored within the base. As you might imagine, there are entire shelves of EE school libraries devoted to this topic; however, there are a few basic principles that can prove handy.

To start, often the best data models reflect the relationships among the real-world objects being modeled. For example, if you were building a base to handle orders, you might have a table of [Orders]. Each [Order] record would be linked to a [Customer] record and to one or more [Line Item] records, If you think of ‘linked to’ as roughly synonymous with ‘contains,’ you will see how such a structure mimics that of a real-life order. (Or, rather, it mimics a real-life order form — which in turn represents the real-life components of an order.)

With Airtable, there is a secondary consideration, as well: Because the data model influences work flow, the relationship among tables should also reflect the actual process in use. Fortunately, ensuring your data model accurately represents both the objects modeled and the process supported is far more intuitive than you may fear.

For instance, let’s look at your base. A fundamental function of most bases is the creation of a new… whatever — as in ‘whatever your base is designed to track and monitor.’ I assume you do not create a new parent or child record every time someone is born, on the off chance they will some day become the responsibility of the agency; instead, I would guess the usual starting point for normal work flow would be the creation of a case ID.

Where does the case ID reside in relation to other objects in the base? Is there a case ID issued per family unit, per child involved, or per triggering incident (for instance, client contacts agency, or court refers client to agency)? The answer to that and similar questions will help determine the best way to organize your tables.

For instance, let’s assume case IDs are assigned by triggering incident. Accordingly, upon the observance of a triggering incident, the user would create a new [Case ID] record. He or she would do this from the [Case ID] table, making it the base’s primary table, typically the left-most table listed in the base, and procedurally the one from which most user actions originate.

Within the newly created record, [Case ID]’s primary field should be set to a unique identifier, most likely the table autonumber alone or in combination with elements of the current date. While you conceivably might have an {Incident Type} field (either a linked record or, more likely, a single select) allowing for a standardized grouping of incidents, {Incident Description} would probably be a long text field specific to the particular case.

After the incident itself, the next-most important data for the [Case ID] record to maintain would be information regarding the people involved. Here I follow your lead, referring to ‘parents’ and ‘children.’ However, rather than create separate [Parent] and [Child] tables, I’d be tempted to create a single [People] table that contains information on parents and children alike; the moment you decide differently and create a dedicated table for each class of person, rest assured someone in the [Child] table will have children of his or her own. Accordingly, there would be two one-to-many linked records defined in [Case ID], both linking to [People]: {Parents} and {Children}. In addition, each [People] record would have two one-to-many linked record fields defined that self-link back to [People]; these would be used to establish links between each person and his or her parents and/or children.

In theory, you might link [Case ID] only to the parents or children involved, and let the {Parents}↔{Children} links within [People] handle the reciprocal link. I would recommend against such an approach, though, as conceivably there could be a number of such links defined for a given individual, with only a portion applicable to any specific [Case ID]. (For instance, one [Case ID] might track an incident between a parent and biological child, while a later one might involve the parent and his or her step-child.)

Speaking of… should {Parents}↔{Children} not provide the level of detail needed for data analysis and planning, a join table of [Relationship Type] should be created. Each [Relationship Type] record should consist of two linked record fields, {PersonA} and {PersonB}, each linked to [People], and a single-select field, {AtoB}, containing possible reciprocal relationships: ’Parent↔Child’, ’Step-parent↔Step-child’, ’Adoptive parent↔Adoptive child’, and so on.

Throughout, of course, you should make as thorough a use of lookup and rollup fields as possible to prevent redundant data entry. For instance, once you create a [Case ID] record and click the plus sign under {Case ID::Parent} or {Case ID::Child} to link to a [People] record, any data concerning the person should be accessed through a lookup field rather than entered manually. A good rule of thumb is if you have to enter the same information twice, or if you have to enter data that already exists elsewhere in the base, something is wrong with your data model. (That said, there are instances where Airtable forces redundant data entry — most notably, when one needs the value of a formula field to be mirrored by a single-select field to take advantage of processing magic Airtable only grants selects, such as designating kanban groups. In such instances, one should create an {Alert} formula field that, if two supposedly duplicate fields contain values that do not match, result in an error message being displayed.)

Finally, you may wish to create a self-linking [Case ID] field named something like {Master Case}. This will allow Case IDs opened by mistake to be subsumed under the master [Case ID].

Again, many of these definitions and relationships reflect only those I assume the base mirrors; clearly, their declarations will likely need modification in areas where my assumptions were off the mark. Still, with a little give-and-take, the examples provided here should serve as a framework for building an all-encompassing system based upon the interrelations among [Case ID]’s and [People].

I realize you asked for a sip of water, and I handed you a firehose. :slightly_smiling_face: I assure you, as you work your way through your first few attempts at creating a base, you will find the task becoming increasingly intuitive. If you are building a base to accompany or replace an existing process, a good place to start is by printing out a copy of every email, form, request, order, and so forth used in performance of the process. That should give you a good feel for how various data structures contain or relate to others, as well as an understanding of what the source will be for assorted data.

If the base provides entirely new functionality, walk through each process mentally several times, providing as disparate an answer as possible at each decision point. For instance, if on the first pass you mentally walk through creating a case ID with one parent and one child, on your next pass consider how one would handle a child with three parents in the system, two biological and one step. Or consider how to handle a situation when two triggering events occur and are logged nearly simultaneously. The better a feel you have for the way data interact, the more confident you will be in your modeling.

And, finally, keep this in mind: You’re going to get it wrong. That’s not an Airtable truism: It’s a truism of virtually any attempt to capture a process or create a data model. The human mind excels at filling in gaps and missing structure — which cuts both ways: On one hand you may discover your process goes from A to C through an implicit B that must be made explicit for the base to work; on the other you may find you have defined an overly detailed structure (foot bone→ankle-bone→leg bone→knee bone→thigh bone→hip bone, when all you needed was foot→leg→hip). If you are like me, somewhere in that final sprint, as you’re closing out the last stretch with no more than 5% or 10% remaining, it will suddenly occur to you that by making a few minor changes to the data model — and, of course, rewriting a third of your formulas and repopulating most of your records — you could reduce the base’s complexity, accelerate its processing, and make it more foolproof.[1] Fortunately, Airtable is very forgiving when it comes to making substantial architectural changes to a base. (Even so, I recommend making a duplicate of the base first; I will also duplicate a table prior to making any particularly hairy changes therein. And don’t forget the belt-and-suspenders-with-a-jumpsuit approach: Make sure to save a snapshot of the base before duplicating it.)

On looking over what I’ve just written, I realize I’ve said “it’s absolutely critical to get your data model correct” and “don’t worry about getting your data model correct, because you won’t.” In retrospect, I find I agree with both of those statements: Starting with — or, at least, arriving at — a robust, well-organized data model will make data entry, upkeep, and future expansion of the base much, much easier.

At the same time, though, once you feel comfortable with your model, go for it. Sure, you’ll probably find things you missed or places it can be improved — but it’s not as if you’re hand-carving this from blocks of SQL. As you find places where the data model needs revision, Airtable typically allows you to do so, quickly and easily, even on a live base. So, sweat the small stuff — but don’t sweat the small stuff too much.
. __________

1. Of course, sometimes it takes longer to discover potential improvements; in my case, it often takes my becoming a better Airtable developer before I realize what it was I should have done. At times, the realized benefits are substantial: For instance, I recently made a few relatively small changes to my de-duplication routines that had the effect of trimming unnecessary data comparable in size to four copies of War and Peace from the example base. Two things to keep in mind: First, even with the less-efficient data model, the base performed as intended, albeit not as peppily as it does today. And, second, once I had envisioned the possible modifications I could make, it took less than 10 minutes to implement the new model on a duplicate of the base.

Thank you both - I dove in and am going through this & learning as I go. I guess the hardest part is that the courts are extremely redundant in that they ask for the same info in multiple ways. That’s going to make datamodeling a big deal.

the same info in multiple ways

That screams ‘views’ to me — or maybe a combination of views and Blocks. There was a terrific post recently on using Airtable to manage script coverage better — which I realize has absolutely nothing to do with your application. However, if you scroll to the end, it includes a wonderful example of how one can use the Page Designer Block. (I don’t know who was responsible for that implementation, but it’s one of the best arguments I’ve seen so far for upgrading to a Pro-level account.) I suspect you could craft a number of court documents (or, maybe, attachments to court documents) using this tool.

You might also want to look at the second Page Designer Block defined in my ‘Black Mirror’ base in Airtable Universe. In it I make use of Airtable’s text merge feature that allows one seamlessly to fold values from the base into blocks of text for a more narrative presentation. Depending on the precise requirements of different courts or different agents of the court, you may be able to exploit this feature to allow you to say the same thing multiple ways…

Thanks for all the great info.

As a follow up:

Let’s say I wanted to aggregate all my data from all the tables into a single place. All based on a primary key, of course (in my case: Matter_ID). If I were doing this in PHP, I could write a script that outputs all data related to that Matter_ID key. How would I accomplish this in Airtable? Is there a way to say: Show me everything where Matter_ID=2632.01?

I hope that’s clear.

Thanks again for all the help. I find myself stopping wondering at points because I’m not used to interacting with databases visually.