One to many relationship is there a limit?

[Kim_Trager1]

Is there a limit for how many links you can have from a one to many relationship?

[WilliamPorter]

Kim,

There is no logical or theoretical limit (in database theory). In heavier-weight database platforms, tables with millions or even many, many millions of records are not uncommon, but usually you run into performance problems before you run into logical limits.

But there is another consideration in Airtable: the pricing rules. Airtable’s pricing permits max of 5000 rows (records) per base in a Plus account and 50,000 rows per base in a Pro account. My understanding (which could be wrong) is that this is not the max per any single table, but the max total row count across all tables. If this is correct, then for a user with a Plus account, if the base had just 1 row in the Parent table, the limit of links to the Child table would be 4,999. Add more rows to the parent table each with at least 1 link to the child table and the max links for any individual parent row is going to go down. Make sense?

William

[Kim_Trager1]

Hi William

Thank you for this.
It does make sense… Currently making a base where I’m cleaning up 17.000 existing records from an existing excel sheet. And it was when I tried to convert a name field for suppliers to a linked field which in would create the supplier name in another table link to it that it seemed to be some limit around the 249 mark.

When I would create a blank record and try link to a supplier that already was linking to 250 records I would not be able to find that supplier. Which I thought might be a limitation?

[WilliamPorter · Accepted solution]

Kim,

Okay, it sounds like you’re saying that, when you tried to link a parent record to a new child, if there were already 250 children linked to that parent, you felt that you were prevented from adding any more. Is that right?

On the assumption that I’m understanding you (please correct me if I’m not), I did a quick test. I have an Airtable base that contains the 10,000 most commonly used passwords (per Wikipedia). So it has 10,000 rows in a table named PASSWORDS.

I added a new table called CLASS, with the primary column containing just 2 records:

Top 100
Next 900

Created a “link to another table” column in PASSWORDS. Linked first row to “Top 100” in CLASS table, then copied that value and pasted it into the next 99 rows. So far so good.

Next I linked password row #101 to the “Next 900” record in the CLASS table. Once again, I copied that cell and then pasted it into the next 899 rows. Ended up with 900 password rows linked to the “Next 900” record in CLASS.

Finally to test what I think is the problem you are reporting, I opened up the Next 900 record in CLASS table. This is what I saw:

Now I clicked on “Link to a record from Passwords” and I saw the list of available records in PASSWORDS. Scrolled way down, through thousands and thousands of rows. Looked like this:

And I was able to add link another record to my “Next 900” class, which is now misnamed because it now contains 901 rows.

So it certainly appears that there’s nothing remotely like a 250-child limit. I will admit one thing though: Scrolling through those 10,000 rows was a slog. Well, it started out pretty well, but the reason I stopped around record 8278 was that the scrolling was getting tiresome. But I wasn’t using the “find an existing record” option and no doubt that might have helped.

Make sense?

William

[Kim_Trager1]

Hi @WilliamPorterTech

Yes it’s exactly what you describe above.
Not sure if it is an airtable bug, it happend twice now after using the dedupe block. What was weird is could roll up a name in via the linked field in the linked record, despite not having any records linked. So basically the record seemed to be linked to a record but didn’t show the linked record.
When I deleted the record and made a new one I didn’t seem to have the same problem.

Thanks for testing this out.