Linking across multiple tables using the same fields

[gabriel]

Hello,

Here’s some preliminary information to help you help me

I have four tables within one base:

1. Companies (primary field: Company Name)
2. Articles (primary field: Article Title)
3. Topics (primary field: Topic Name)
4. Authors (primary field: Author Name)

• In the Companies table, there is a Topics field linked to the Topics table, and an Authors field linked to the Authors table.

• In the Articles table, there is a Topics field linked to the Topics table, and an Authors field linked to the Authors table.

The question

How can I link associated Authors to the Topics table – taking into account the relationship between the Authors and the Topics on the Companies and Articles tables?

And how can I link associated Topics to the Authors table – taking into account the relationship between the Topics and the Authors on the Companies and Articles tables?

Thanks a lot in advance :raised_hands: :slightly_smiling_face:

[gabriel]

My goal is to associate Authors to Topics in both the Authors table and the Topics table.

The problem is that Topics can be associated to Authors on other tables as well, for example the Articles table there can be an Author associated to an Article and to a Topic unique to that article.

So, in the Authors table a should be able to see all Topics associated to that Author regardless if it came from an Article, a Company, or a Topic by itself.

John can write about History, but he could have written an article about Technology. So both, History and Technology should be associated to John.

[Justin_Barrett]

I think you’re making this setup more difficult in your mind than it needs to be in reality.

Here’s the great thing about links: when you link a record in [Table A] to a record in [Table B], and that record in [Table B] is linked via a different field to a record in [Table C], you can see every detail you want about the record in [Table C] via the record in [Table A] even though there’s no literal link between A and C. This can be achieved by opening the “card” view of any record by pressing the Space bar with any field active, or clicking directly on a link. In that popup card view, find a link. Click to open its card. Find another link, click to open its card. And so on.

Long story short, you only need to build links between tables where they make logical sense in the setup phase. Later on, you can follow the link trail as you need to find related records in other tables.

Does this help?

[JonathanBowen]

Hi @gabriel - when I’m building a base, I will often do a very brief “design phase” on paper just to give me a sense of how everything relates. For the entities you are trying to track in your base, I might come up with something like this:

Sometimes, I’ll do the “proper” one-to-many, many-to-many thinking and this can be helpful, but sometimes isn’t necessary.

Thinking about your scenario, I’m starting with Authors. Authors write articles, so an author can have many articles. Equally a single article might have two or more authors, so we need to allow for this. Each article will be on a certain topic (potentially, might be classified in multiple topics), so there will be a link there. I’m not certain from your description now companies relates to any of the other entities, so I won’t do anything with this for now.

What you can see from the diagram (and as stated by @Justin_Barrett) different entities have can have a relationship through another entity, so authors are related to topics, not directly, but via the articles they write.

All of the above would lead me to a base like this:

You can also have a field on the Topics table which is a lookup of Authors (from Articles).

From your description, it sounds like an author could be related to a topic independently of an article. For this, I think you’re going to need another field directly linking Authors and Topics:

Not sure if this fully answers your question, but hope it helps!

JB

[gabriel]

Hey @Justin_Barrett

Thanks for the reply, really appreciate it.

Indeed, you are right. You got it.

But for visualization purposes and data digestion, we’d like to achieve the goal described in the question.

Any ideas as to how we could achieve it?

[gabriel]

Hey @JonathanBowen

Thanks for the reply :slightly_smiling_face:

I think you hit it with your last screenshot! I might need to separate “types of Topics”
But how would you go the other way around?
Having Authors relate to single topics…?

Let me know if it would be helpful for you to see the actual Base :pray:

Cheers,
Gabriel