Help with contacts, organizations, and their affiliations/relationships

Hello,

I’m working on my current doc setup for my base tables, and I was hoping to get some feedback on how I should setup the base tables for contacts and their relationships to each other and to organizations.

Setup 1 is what I currently have in my doc. I used a setup that I found in the gallery, so I don’t really understand how the formulas work. With this setup I have a table for Contacts, Organizations, Relationships (organization to organization), Affiliations (Contact to Organization), and Relationship & Affiliation Type DB. With this setup I have two columns in the Contact DB. One shows a Contact’s Relationships and another for their Affiliations. The Relationships column accounts for the Contacts listed in Primary Contact and Related Contact

Setup 2 is more what I’m looking to do. It has relationships and affiliations in one table and then I would have one column that pulls in all all relationships from the Affiliations & Relationships table. If I do it this way, the formula for naming the columns and showing their relationships is going to be more difficult to put together.

My goal is to streamline my Coda setup, starting with having as few base tables as necessary. Do you recommend combing these tables? If you do, how would I go about tackling the new formulas?

Thank you so much for your help!

Hey Tanya, thanks for posting here! From what I can see, either setup would theoretically work and both seem like good foundations. It’s also worth mentioning that fewer base tables isn’t always the most ideal setup; more base tables often allow for more flexibility. And if one setup requires more complex, heavy formulas, that could lead to issues down the line. So it largely depends on preferences and details of your setup.

To help you decide between the two setups, one question I might ask: do relationships and affiliations always have the same qualities? In other words, do you need the same columns for relationships as you do for affiliations? If yes, then I think setup 2 might be simpler for you. But if relationships vs. affiliations have different properties/columns, then I’d suggest separate tables for each (in other words, setup 1).

If you have haven’t checked out our YouTube videos on Designing Docs, I recommend doing so. The playlist covers different schemas and how to choose the right schema for you.