Need help with the concept of how to construct my base

[Ben_Moodie]

I want to build a database for a specific purpose and am struggling to think through the theory behind it.

I need to record names and contact information for a list of people. Each person will also be assigned to a group, with its own information. Each person will also have a record of activities completed for each month. Later, I would then like to visualize trends and averages for activities completed for a given group or individual.

Can anyone tell me hoe they might layout their database to achieve these results?

[Mohamed_Swella1]

Hi @Ben_Moodie,

Welcome to Airtable Community !

To build a Base, you need first to think what is the outcome you are looking for. For example, it seems you need the following Tables:

1. Groups
2. People (with a link to Groups)
3. Activities (link to People, lookup Group) - this should include the dates so you can do the trends.

You can then use the Chart Block on the Activities table, to show the required trends and Group by either Group or Person.

This is just an idea and you can find so much more to do once you start.

BR,
Mo

[Ben_Moodie]

Thanks @Mohamed_Swellam This is definitely helpful, and I think i understand the concept behind it. As I imagine it then, the one table (ACTIVITY) will be huge and almost illegible, but using filters and views will make it manageable I assume. Is that what you’re thinking?

[Mohamed_Swella1]

Yes this is what i had in mind @Ben_Moodie

[WilliamPorter]

Ben_Moodie:

Thanks @Mohamed_Swellam This is definitely helpful, and I think i understand the concept behind it. As I imagine it then, the one table (ACTIVITY) will be huge and almost illegible, but using filters and views will make it manageable I assume. Is that what you’re thinking?

@Ben_Moodie,

@Mohamed_Swellam has already given you a good map. I just want to add some comments in response to the question I quoted above.

.

Re: views and filters

1. Airtable depends very heavily on views. Views are defined mainly by the fields that are displayed and possibly by the presence of one or more filters. It’s easy to imagine an Airtable base that has twenty, or fifty, or more views. The Airtable user experience is largely a matter of users selecting which view conforms best to whatever it is they need to see and do at a given moment.
2. For that reason, it is imperative that you come up with a logical and consistent view-naming system as quickly as possible.
3. Filters are ‘public’ in their effect, not private to the current user, that is, if you define a view that uses a filter to show contacts in the state of Texas, that filter will determine what anybody using that view will see. A filter is not a private find or search.
4. Technically, filters can be edited by anybody accessing the base with editor privileges. But because of the preceding point (filters are global in their effect) you should regard filters as fixed elements of your base and train your users to understand views in this way. If you have a view named (say) “Customers - Texas” and one of your editor users changes the filter to show customers in California, then your UI has broken, in the sense that it no longer makes sense. If you need to add a special view for California customers, duplicate the Texas view, give the duplicate a new name, and change the filter for the new view, as appropriate.
5. That said, in Pro accounts, there is a Search block that will return a “private” result, that is, a result for the current user. For example, a user in the “Customers - Texas” view could use the Search block to find customers in Dallas or Austin. The list that gets returned by a Search block is private to that user and wouldn’t be seen by other users unless they did the same search. The Search block returns its results in a separate window.

.

Re: your “huge” Activities table

1. Don’t know what you mean by “huge”. In the other world where I do most of my work, “huge” would suggest millions of records. In Airtable, “huge” might mean no more than thousands of records. Keep in mind Airtable’s record limits. If you expect to be creating 100,000 activity records a year, Airtable might not be the right tool for this job.
2. If you do start to run afoul of Airtable’s record limits, you might consider duplicating (your base) and renaming the second copy “(Your Base) ARCHIVE”. Create a filter that displays the records you don’t need to keep in the active base, copy them all, paste into the archive base, then come back to the main base and delete them all. BE CAREFUL when doing this.

Good luck.

William