Architectural Advice: Connecting 20+ Years of Data Across Multiple Bases

Start with a single well structured Airtable base, since your main goal is to track one person across multiple roles over time, and linked records only work within the same base.

Create a central “People” table with a universal unique identifier, so individuals can move from student to alumnus to mentor without duplication.

If you later need multiple bases, keep one source of truth “People” base and sync it outward, but be aware that syncing is mostly read only and can limit flexibility.

… Sent it so quick ...

You should create a single, universal "Person ID" for everyone in your system, not just students. This can be an auto generated value (like P-000123 or a UUID) stored in a central "People" table and used as the primary identifier across all roles. While you can still store the University ID for students, it should just be an additional field, not the main key.

Use the Person ID as the source of truth, and link all roles (student, alumnus, mentor, founder, ... etc.) back to that same record. To reduce duplicates, you can also add automations or checks that flag potential matches based on university ID, or email or name when new records are created.

Here few ways to create unique ID:

1- Using automations:
(Best for long term, multi base, or integrated systems)

const uuid = crypto.randomUUID();
output.set("uuid", uuid);

2- Using formula field:
(Fine if everything stays in Airtable and you want something quick)

"PID-" & RECORD_ID()

… Ok I am done this time ;) ...

​@coderkid  Thankyou for your reply. But my main issue is the huge data for almost 20+ years and if we maintain a huge central “People” table and even if I have the advanced plan(Enterprise plan) in Airtable, will the data be sufficient based on the hard record limits?

Single Base vs. Multiple Bases: Given the volume and variety of data, should we aim for one "Mega Base" or multiple specialized bases?

This really depends on how much data you have, and I’d suggest you do a rough consolidation of your data to see how much of each type of data you have by counting the rows.  It feels like the two main limitations we’d have to work with are:

1. Record limits per base
   1. 125k on Business, 500k on Enterprise
2. Record limits per table
   1. 125k on Business, should be the same for Enterprise
      1. See documentation here

And so if you have >125k of one type of data, or >500k records total then you’ll need to look at using HyperDB 

There are tradeoffs for single vs multiple or single with HyperDB and so if you could provide rough numbers for the number of rows you have that’d be helpful for thinking about this

---

re: Unique identifier

The workflow here is that you have them as a record in the ‘People’ table, and then you basically need to find their record and update their data, right?

If so, it feels like your best bet here is email and then if that fails, first name + last name + birth year? 

1. Since you want to track a participant’s journey, you will need linking between different records. The ideal approach for such cases is to maintain a single base. A base is pretty similar to a ‘Database’ where you define different entities (Tables) and relationship between those entities. You can not define relationship between entities lying in different bases or databases.
2. There are multiple approaches to handle uniqueness. The most basic is using an autonumber field in Airtable to generate unique number for every record. But with this, you will also need to enforce an automation to check uniqueness with a certain identifier like an email or phone number. The autogenerated number will always be unique but to ensure uniqueness of records, you need to define the unique paramter first. Let’s say the unique parameter is email, then before inserting any new record in the table, you need an automation to check if this email already exists in the table or not. If no, then insert. If yes, then flag the error.
3. If you take the syncing and linking route, you will have to write a lot of automations to “link” records and ensure data integrity. Within same base, linking is straigforward. That is why I’d ideally not take this approach at all.
4. Airtable has hard limits on number of records on all the plans. So choose a plan accordingly. 

PS: I’ve worked on a similar structuring for an educational organization.

Thankyou ​@anmolgupta, ​@TheTimeSavingCo ..Great Responses. Will get back again once I have done an initial record counts. Appreciate your efforts and time :)