Trouble wrangling a sometimes "1 to Many" situation

[Peter_Steinberg]

Hello helpful community! So here’s the deal:

We’re a small Venture Investment syndicate.

Among other things, we’re using AirTable to track investments into companies by our members.

Usually it’s pretty easy:

John Doe is a Member record in our Members table.

Company X is a Company record in our Companies Table.

The “2019 Series A Investment into Company X” is a Deal record in our Deals table (with Company X being pulled in from the Companies table of course).

For each investor (from our Members table) who chooses to invest in a Deal we create a new record in the Investments table. It’s the intersection of a Member with a Deal… with some additional info added, such as the amount they invested, the date the funds arrived, the wire transfer info, etc.

With me so far? Great. So here’s the problem. Not ever investment is made by a person. Some investments are made by legal entities like Trusts or LLCs. Of course those legal entities have points of contact like a name, phone number and email address. And to make matters worse, some points of contact manage more than one legal entity. So we might have investments made by John Doe, John Doe’s Living Trust, and John Doe LLC… which are really all the same person.

I can create an Legal Entity table where each entity has a record and all the associated contact info is listed, but then in my Investments table I would have some investments being made by a Member and some by a Legal Entity and I don’t know how to pull info from 1 of 2 different tables.

The only solution I’ve come up with is to create a record in the Legal Entity table for every single regular member, even though it’s just a duplicate of a lot of their info in the Members table. And then I’d pull info into the Investments table from the Legal Entity table, not the Member table.

This seems like a lot of duplicate data when only about 5% of our investments are made by these annoying non-people.

Any suggestions?

[Justin_Barrett]

Rather than messing with yet another table, consider adding legal entities to your members table. Along with that, you would add a {Representative} field (or whatever name makes sense to you). That field would be a link to another record in the same table. It’s easy to only think of the Link field type pointing to other tables, but same-table links are also possible.

With that setup, you would begin by creating a record for John Doe the person, with all of his contact info. Because John Doe is his own representative, the {Representative} field would remain empty. If John Doe works through a legal entity like John Doe LLC, you’d make a new record for that entity. It would be mostly empty, and you would use the {Representative} field to point to the original John Doe record. There’s no duplicate contact info, and the link to the main John Doe record makes it easy to look up John’s contact info when needed.

Would that work?

[Peter_Steinberg]

Justin, thanks so much for the prompt input. Very appreciated.

Indeed, I didn’t know you could link to a record in the same table, so at a minimum, thanks for that!

This is intriguing. I just set it up like this and it certainly solves the problem of creating duplicate data. But it might not solve all the problems. Off the top of my head:

– We will want to email everyone in the Members table. Since “John Doe” will have an email address but “John Doe Living Trust” will not – it will just have a John Doe showing in the {Representative} field – that shouldn’t be a problem. But what happens when John Doe provides us different email addresses for the different legal entities? (I’m not sure this has happened yet but I’m sure it will.)

– Similarly, what happens if we want to email everyone with an investment in Company X? The ** Investments** table shows an intersection between a Member and a Deal. But sometimes the member will be an “Entity” which has an {Representative}, but the entity doesn’t have any other info associated with it so how does the email address carry over?

– The last challenge that comes to mind is that I embraced AirTable for all of this because we hope to use it to generate individual investment value reports. Each row would be a an investment you’ve made, with how much you put in, when you invested, what it’s worth now (on paper at least) and what it multiplies out to. Then we’d total all the rows fo ra grand total of what you’ve invested and what it’s worth. With some investments made by Members and some made by entities the member manages, I’m not sure this new structure would roll up properly (and I use ‘roll up’ in gene generic sense, not necessarily the RollUp sense.

So… any further guidance? Or do these additional requirements break your suggestion? Anyone want to weigh in?

-Peter