One base for all people and payee query

[Rosa_Ramos]

Hi everyone,

I have a table design query in 2 parts.

Firstly, I’d like to know your opinions about creating a ‘people’ table in a base - is it best if it is one table or many? For example, I have people that are:

• ‘students’ taking classes,
• ‘payees’ that might pay for those students’ classes (some students pay for their own).
• ‘company contacts’ who normally don’t take classes or pay (but work for companies that do pay)

If I create 3 separate tables, all these 3 groups of people will require similar fields in each of those tables and a payee could also be a student themselves… also a company contact might also be a student so I think that one table would probably be better with each person marked with a different (or multiple) ‘person type category’ (in the case of students who are also payees for example). Is there any reason/advantage to keeping these different types of people in different tables?

The second part is payment related:

Classes can be paid for either by the students themselves, by payees who are not students or the accounts department of a company (not necessarily through a named company contact, in fact hardly ever through a named company contact). I have another table set up called ‘Companies’ that could be linked to either the students or the company contacts.

If I create a payments table to record who is making the payment for the class and create a ‘payment made by’ field I can either select the student if they are making the payment themselves, or another person from the people table, but how would I select the company if it is actually a company making the payment? What would be the most logical/best solution here? I can think of different ways to do it, but want to know how someone with more experience would do it.

Many thanks.

[Nathaniel_Grano]

Hi @Rosa_Ramos,
I think you are already thinking about this in a very sensible way!
I don’t believe there is a single “right answer” to either of your questions. It is a matter of trade-offs based on what you plan to do now or in the future.

If you have a lot of fields in common between students/payees/company contacts, then it might make sense to have a single “people” table. When there are fields that only certain types need (like “Company”), you could either:

• Have the People table be a superset of all the fields a person might need, or
• Create additional “wrapper” tables: Students, payees, company contacts
  • Each wrapper table would link to a Person (and look up whichever person details are relevant) and then add on the additional columns needed for this type

Given that Airtable makes it easy to have a field link to a single table but not link to entries from multiple tables, I suspect the first approach is simpler for most use cases.

For your second example, given that you have a lot of Payees that are neither companies nor students, it might make sense to have a single Payee table set up something like this:

• Payee name (formula that picks the name from one of the following columns depending on which one is not blank)
• Link to Student (fill in if student is the payee)
• Link to Company (fill in if company is the payee)
• Other Payee Name (text field, fill in if payee is neither student nor company)

Then each Class can simply link to one Payee.

[Rosa_Ramos · Accepted solution]

Hi Nathaniel_Granor,

Yes, there seem to be so many ways to do this that trying to settle on one way that will work into the future seems a bit daunting! I’m glad that I was on the right track. Thank you for coming back to me so quickly and for describing how to set up the single payee table - it makes a lot of sense. :slightly_smiling_face: