Problem with Database Architecture

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 

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.

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

@Mike_AutomaticN I just sent you a DM. Thanks so much for offering to hop on a call! I truly appreciate it.

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.