Best Approach for hundreds of daily entries

[Honey_Syed]

Hello, I am trying to build a content tracker and there are going to be hundreds of entries every day.

I will be creating views where people can filter post type with date.

For Ex: I want to see “query” posted on 4th December.
I want to see “post” posted between 4th December - 7th December.

Should I create a new base for each date or should I continue updating the same base with hundreds of thousands of entries?

Or is there a better way to do this that I am totally missing out on?

Thanks

[ScottWorld]

Welcome to the community, @Honey_Syed!

Yes, all of your daily entires should be in the same table.

However, you can’t have hundreds of thousands of entries in Airtable in a single base. There is a maximum of 50,000 records per base (not per table) if you’re on the Pro plan, and a maximum of 100,000 records per base (not per table) if you’re on the Enterprise Plan — which costs $3,000 per month.

So once you hit the limit, you would need to figure out some way of deleting old records, or moving old records to another base. This is typically done with an automation tool like Integromat. I assume you could automate this with a custom JavaScript as well. @Bill.French has also come up with some cool ways of storing old records as JSON data within a long text field, which would workaround the record limitations.

Although if you’re really looking for an enterprise-level system that supports hundreds of thousands (or millions) of records, I would urge you to explore other platforms as well. For example, FileMaker can support billions of records, and it costs a tiny fraction of what Airtable’s enterprise plan costs (i.e. it’s 90% cheaper).

I am a professional consultant. My 3 platform specialities are Airtable, FileMaker, and Integromat. You can check out my website at scottworld.com. Feel free to contact me if you need professional consulting/development work.

[Bill_French]

Honey_Syed:

Should I create a new base for each date or should I continue updating the same base with hundreds of thousands of entries?

I think you are conflating table with base. But in case you really want a new base for every day, that is a really bad idea. It’s not as bad as a table for each day, which is simply a lesser bad idea. :winking_face:

Your question cannot truly be answered in a helpful way until we know more about how the data needs to be used. You mentioned a query of all posts between two dates. If the posts are segregated by table or base, you will never get to see the results of such a query. Queries are designed to act on tables and views and the only way to blend or merge queries across multiple tables or bases is to build a query app that can proxy your data stores into a unified view.

I recommend you share with us how the data needs to be consumed before asking us how the data model should be designed. Failing to develop data use requirements and envision how information must be employed is how many solutions wind up painted into a corner.

UPDATE: From the looks of your data, it seems that these are log entries being generated from an app of some sort. Log data is typically voluminous (as you indicate) and Airtable is not the best storage model for logs. However, as Scott mentioned, imagine a day’s log entries in a single record.

This is an approach that allows you to store roughly 100k of log data in a single record as a JSON object. Given the data I see, the records are likely to represent a max of 300 bytes of data per entry. With that, you could store roughly 300 log entries in a single record and perhaps 500 log entries per record if compressed.

This approach (discussed with some detail here) does create other issues and responsibilities such as transforming the JSON objects into discrete entries when querying the data. But this can be overcome with scripting, etc.

This strategy will be of no use if your daily log events are greater than 300 events per day.

Suggestion - don’t use Airtable. Use a system designed for logging and analytics like ElasticSearch (free and open-source). Logs typically have no benefit being stored in systems that offer traditional database features such as relational modeling, form-handling, etc.