- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 17, 2025 09:27 AM - edited ‎Jan 18, 2025 03:00 AM
Hi there,
I'm facing an architectural problem for which I haven't found a solution as of yet. This problem is linked to the way I handle revenue and payment depending on the client type – small company or big company.
Here's how I decided to structure my database to handle revenue and payment for small companies:
And here's how I decided to structure it to handle the workflow for big companies:
But this leads me to the following problem in terms of database design.
- breaking the [Revenue Items] – [Client Sessions] direct relationship
- pulling the Client Sessions into the [Revenue Items] table with lookup fields and assigning each of them to the proper revenue item in dynamically filtering them based on their execution date
Since artificial intelligence couldn't offer a satisfactory solution to that problem, I'm now resorting to natural intelligence! Any smart database architect out there who's got a suggestion?
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 17, 2025 11:33 AM
Lol, loved the natural intelligence reference @JBJ.
Might need some more context on your operation. I'm available for the next hour or so, feel free to ping me via Private Message and we can hop on a brief call to go over it. Happy to help out!
Mike, Consultant @ Automatic Nation

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 17, 2025 04:55 PM
While I see circular linking in your diagrams, I'm not seeing the circular references (at least not what Airtable calls a circular reference). It seems like the [Revenue Items] table should have links to both the deal and the session tables. You could use a {Deal Type} single-select in the [Deal] table to indicate if you should use the "big client" or "small client" process. You might have two sets of lookup/rollup fields that pull info in different directions and use the {Deal Type} to determine which set of lookups/rollups apply to a particular revenue item.
It isn't clear what you want to put in the [Deal-Week] table.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 18, 2025 03:22 AM
@kuovonne What I called a 'circular reference' is probably a misuse of language on my end. I was referring to a circular relationship structure in my base: table A – table B – table C – table A.
If you tell me that [Revenue Items] should be directly linked to both [Deals] and [Client Sessions], then I'm all good. I'm using a dynamic filter on [Revenue Items] to make sure that the linked client sessions belong to the proper deal.
Thanks for suggesting the {Deal Type} solution. I'm already using a similar field, except that it lives on the [Companies] table ('small company' or 'big company').
The reason why I started questioning my current base architecture is I started learning about database design in SQL to improve my base designing in Airtable. But I suppose SQL principles don't have to be all applied to Airtable if we can easily dispel data integrity risks with field filtering and if applying them should comprise the easiness-to-use. That being said, if you have a good resource to suggest for proper database design specifically in Airtable, I'm all ears.
The reason why I created a [Deal-Week] junction table is to get that type of list view on a deal record details page.
Thank you for your help and prompt reply.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 18, 2025 03:24 AM
@Mike_AutomaticN I just sent you a DM. Thanks so much for offering to hop on a call! I truly appreciate it.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 20, 2025 09:24 AM
Okay. Circular relationships are different from what Airtable calls a circular reference. In Airtable, a circular reference usually refers to a set of calculated fields where each field is used as the input to another field in the circle. Airtable will not let you save a formula field that will result in a circular reference.
Circular relationships are sometimes created where a lookup or rollup fields would be more appropriate. However, that does not mean that all circular relationships are bad. Sometimes there are good reasons for circular relationships.
It is harder to tell what is the right schema for your base. My first impression of your base was that the schema (and possibly the rigidity of your billing method) felt off, but without more info, it wasn't worth digging into it, and I focused on your specific question instead.
Most of the concepts of database design that come from the SQL world transfer over to Airtable. If you know how to normalize a traditional relational database, those skills should transfer to Airtable as long as you understand the differences in how Airtable manages relationships. You have to find the right balance between simplicity and complexity when maintaining database structure and real-world situations.
As for the [Deal-Week] table, I think my confusion is that I'm assuming that sessions can take place in different weeks from when the deal is closed. It isn't clear if a session should be linked to the week in which it occurred or in the week that the deal was closed. This might be a naming thing.
