Junction table creates two new columns

[Corey_Luskin]

I have a table of Companies. Within this table, some are “Operating Companies” and some are “Investors”.

I want to create another table of Holdings, with two main columns that link back to first table. Column 1 is “Operating Company” and Column 2 is “Investor”. The purpose of this table is to capture which investors own a stake in which OpCo’s. The primary key here is simply a concatenation of the two data fields, OpCo and Investor.

When I create this table, though, it introduces not one, but two new columns into my Company table. The first new column is “Holdings” and the second is “Holdings2”. I would rather have just one column for Holdings appear in the “Companies” table.

I guess I understand what is happening. If I were to separate OpCo’s and Investors into separate tables, then each of those tables would have a single column linked to Holdings table.

But there are other advantages to having all Companies in single table, so I would rather not split them off just yet. But it seems messy and inconvenient. Has anyone encountered this issue?

[David_Skinner]

That is how Airtable works. When you create a Linked Record field to another table, then it creates a mirror field in that other table. Surely you can just hide these fields if you don’t wish to see them?

Regardless, it sounds like you should almost certainly have Investors and Operating Companies as separate tables. Whatever “other advantages to having all Companies in single table” there are will very likely be outweighed at some point by the negatives created by having a poorly structured database.

[Corey_Luskin]

Thanks, David. That is helpful. I will try your suggestion.

Let me run the “other advantage” by you, to see if you have any thoughts:

I have an entirely separate table called Contacts that holds individuals and their personal data. For each of these rows/people, I need a column that conveys which Company they are with. This, naturally, is a link to my Companies table. Some of these people are with Operating Companies and some are with Investors. Since OpCo’s and Investors are together in one table, my Contacts can just point to that same table.

But if I split OpCo’s and Investors into separate tables, than my Contacts table needs an extra column so that it can point to both of these. It feels less natural.

[David_Skinner]

I think you need the extra column in the contacts table. This would also make it easier for filtering and searching your contacts table.

These extra columns that are created are just a quirk of how Airtable is built. My bases are littered with them. It is best to just hide them when they seem like they are in the way.

[Corey_Luskin]

Thanks so much. Really appreciate it.