Skip to main content

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.

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.

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.


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. 🙂

Reply


Terms & Conditions